Skip Headers

Oracle® interMedia User's Guide 10g Release 2 (10.2) Part Number B14302-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 3 Media Upload and Retrieval Web Sample Applications

This chapter describes the development of several types of media upload and retrieval applications using Oracle interMedia ("interMedia") object types.

Section 3.1 describes the development of the interMedia PL/SQL photo album sample Web application that uses the PL/SQL Gateway and PL/SQL Web Toolkit for Oracle Application Server and Oracle Database.

Section 3.2 describes the development of the following interMedia photo album sample Web applications that use the interMedia image object type:

- Java servlet application that uses Oracle interMedia Java Classes for Servlets and JSP (see Section 3.2.1)

- JavaServer Pages (JSP) application that uses Oracle interMedia Java Classes for Servlets and JSP (see Section 3.2.2)

- Active Server Pages (ASP)/Visual Basic (VB) application for the Microsoft Internet Information Server (IIS) Web Server (see Section 3.2.3)

Note: The sample applications described in Section 3.2 differ from the PL/SQL photo album sample application described in Section 3.1.

Section 3.3 describes the interMedia Code Wizard application for the PL/SQL Gateway that uses the interMedia image, audio, video, and heterogeneous media object types.

This chapter assumes the following:

- You are familiar with:

  - Developing PL/SQL applications using the PL/SQL Gateway and PL/SQL Web Toolkit

  - Developing Java-based Web applications using JDBC, creating Java source code, compiling it into byte code (`.class`) files, and deploying class files into respective servlet containers required by Oracle HTTP Server for Oracle Application Server and Oracle Database

  - Developing ASP/VB scripts for the Microsoft IIS Web Server

- You have already installed and configured the following sample applications:

  - Oracle interMedia PL/SQL Web Toolkit Photo Album application

  - Oracle interMedia Java Servlet Photo Album application

  - Oracle interMedia JSP Photo Album application

  - Oracle interMedia ASP/VBScript Photo Album application

  - Oracle interMedia Code Wizard for the PL/SQL Gateway application

  See the `README.txt` file for each respective sample application for installation and configuration information.

## 3.1 Oracle interMedia PL/SQL Photo Album Sample Application

The interMedia PL/SQL Web Toolkit Photo Album sample application demonstrates how to perform the following operations:

- Use the interMedia image object type to upload, retrieve, and process media data stored in Oracle Database.

- Combine the image metadata methods of interMedia with the XML document management capabilities of Oracle XML DB and the full-text indexing and search features of Oracle Text to create a solution that can extract, store, and search metadata that is embedded in binary image files.

- Collect new metadata from a user, format the metadata into an XML document, and store the document in the binary image.

When installed, the photo album application creates a number of schema objects that are important to the following discussion. These objects include the `photos` table, which is defined by the following CREATE TABLE statement:

    CREATE TABLE photos( id           NUMBER PRIMARY KEY,
                         description  VARCHAR2(40) NOT NULL,
                         metaORDImage XMLTYPE,
                         metaEXIF     XMLTYPE,
                         metaIPTC     XMLTYPE,
                         metaXMP      XMLTYPE,
                         image        ORDSYS.ORDIMAGE,
                         thumb        ORDSYS.ORDIMAGE )
    --
    -- storage images with 32K chunk
    --
    LOB(image.source.localdata)  STORE AS (chunk 32k)
    --
    -- but the thumbnails with only 16k
    --
    LOB(thumb.source.localdata)  STORE AS (chunk 16k)
    --
    -- and bind the XMLType columns to the interMedia metadata schemas
    XMLType COLUMN metaORDImage
     XMLSCHEMA "http://xmlns.oracle.com/ord/meta/ordimage"
     ELEMENT "ordImageAttributes"
    XMLType COLUMN metaEXIF
     XMLSCHEMA "http://xmlns.oracle.com/ord/meta/exif"
     ELEMENT "exifMetadata"
    XMLType COLUMN metaIPTC
     XMLSCHEMA "http://xmlns.oracle.com/ord/meta/iptc"
     ELEMENT "iptcMetadata"
    XMLType COLUMN metaXMP
     XMLSCHEMA "http://xmlns.oracle.com/ord/meta/xmp"
     ELEMENT "xmpMetadata";

The data types for the `image` and `thumb` columns are defined as interMedia image object types. These columns are used to store the full-size images and the generated thumbnail images, respectively. The LOB storage clauses indicate that storage for the full-size images is to be allocated in 32-kilobyte chunks, thus enabling the fastest reading and writing of the image data. Similarly, storage for the thumbnail images is allocated in 16-kilobyte chunks, enabling fast access and efficient storage. In addition, using a smaller chunk size reduces the allocation of empty space.

The table also defines four columns of type `XMLType` to store XML documents that contain four different kinds of image metadata. Each column is bound to a specific interMedia metadata schema. Each metadata schema defines precisely the data model of the metadata document. These schemas are registered with Oracle XML DB when the database is created. The column definitions specify that the database uses structured storage to manage the XML metadata documents. Some advantages of using structured storage to manage XML include optimized memory management, reduced storage requirements, B-tree indexing, and in-place updates. For more information about XML DB, see Oracle XML DB Developer's Guide.

When installed, the photo album application also creates other schema objects. These schema objects include two types of indexes that accelerate metadata searches: a `CONTEXT` text index and a `CTXXPATH` text index.

The `CONTEXT` type is a text index over all columns that contain descriptive information about the image. These columns include `PHOTOS.DESCRIPTION`, which is a VARCHAR2 data type, and the following four `XMLType` columns: `PHOTOS.METAIPTC`, `PHOTOS.METAEXIF`, `PHOTOS.METAXMP`, and `PHOTOS.METAORDIMAGE`. The `CONTEXT` text index is used to accelerate metadata searches by implementing the photo album search feature that allows users to search for photographs by keyword or phrase.

The `CONTEXT` text index is created by the following statements. (This example assumes that the photo album application has been installed in the SCOTT schema.)

    -- create preference PA_CTXIDX
    ctx_ddl.create_preference('SCOTT.PA_CTXIDX', 'MULTI_COLUMN_DATASTORE');

    -- create a multi-column datastore
    ctxcols := 'description, '                   ||
                'SCOTT.photo_album.getClob(METAIPTC), ' ||
                'SCOTT.photo_album.getClob(METAEXIF), ' ||
                'SCOTT.photo_album.getClob(METAXMP), '  ||
                'SCOTT.photo_album.getClob(METAORDIMAGE)';
    ctx_ddl.set_attribute( ctxpref, 'COLUMNS', ctxcols );

    -- create the CONTEXT text index
    create  index pa_ctx_idx on photos(description)
    indextype is ctxsys.context
    parameters ( 'DATASTORE SCOTT.PA_CTXIDX' );

For more information about creating and using text indexing, see Oracle Text Application Developer's Guide.

The `CTXXPATH` type text index is used to accelerate metadata searches by allowing users to search only certain types of image metadata as well as limit the search to specific portions of an XML document. For example, the following statements create three text indexes of type `CTXXPATH` to speed up existsNode( ) queries on columns of `XMLType`:

    create index pa_path_iptc_idx  on photos( metaIptc )
     indextype is ctxsys.ctxxpath;

    create index pa_path_exif_idx  on photos( metaExif )
     indextype is ctxsys.ctxxpath;

    create index pa_path_xmp_idx  on photos( metaXMP )
     indextype is ctxsys.ctxxpath;

For more information about creating and using `CTXXPATH` indexes, see Oracle Text Application Developer's Guide.

During the installation, as prescribed by the PL/SQL Gateway, a document upload table is defined by the following CREATE TABLE statement:

    CREATE TABLE PHOTOS_UPLOAD(name           VARCHAR2(256) UNIQUE NOT NULL,
                               mime_type      VARCHAR2(128),
                               doc_size       NUMBER,
                               dad_charset    VARCHAR2(128),
                               last_updated   DATE,
                               content_type   VARCHAR2(128),
                               blob_content   BLOB );

Each image uploaded using the PL/SQL Gateway is stored in the `PHOTOS_UPLOAD` table. An upload procedure (`insert_new_photo`) automatically moves the uploaded image from the specified `PHOTOS_UPLOAD` table to the photo album applications table called `photos`.

### 3.1.1 Running the PL/SQL Photo Album Application

After you have completed the setup tasks and have built the photo album application, including creating a database access descriptor (DAD) entry as described in the `README.txt` file, you can run the photo album application by entering the following URL in the address field of your Web browser:

    <protocol><hostname:port-number>/photoalbum

The `<protocol>` field is `http://` and the `<hostname:port-number>` field is the host name and port number of the system where your HTTP server is running.

When first invoked, the photo album application displays any images that are currently stored in the album. By default, the photo album is empty when first installed. To upload a new photograph, select Upload photo. Enter a description of the photograph and the name of the image file, or browse to its directory location. Then, click Upload photo.

The contents of the photo album are displayed, along with a picture of the new photograph. Click the thumbnail image to view the full-size version of the photograph. When the photo album application displays the text view image instead of its thumbnail image, the image format that was uploaded was not recognized by interMedia. Click view image to display the full-size image.

You can now begin to load your photo album application with your favorite photographs.

### 3.1.2 Description of the PL/SQL Photo Album Application

The user interface for the photo album application consists of a set of Web pages. You can use these Web pages to perform the tasks shown in Table 3-1. The tasks and the Web pages are introduced in this section and described in further detail in the following sections.

Table 3-1 PL/SQL Photo Album Sample Application Overview

User Task | Web Page | PL/SQL Procedures
--- | --- | ---
Browsing the photo album Section 3.1.2.1 | View album Figure 3-1 | `view_album` Example 3-1 `print_album` Example 3-2 `print_image_link` Example 3-3 `deliver_media` Example 3-4
Adding images to the photo album Section 3.1.2.2 | Upload photo Figure 3-2 | `view_upload_form` `print_upload_form` Example 3-5 `insert_new_photo` Example 3-6
Searching for images by keyword or phrase Section 3.1.2.3 | Search album Figure 3-3 | `view_album` Example 3-1 `print_album` Example 3-2
Viewing full-size images Section 3.1.2.4 | View entry Figure 3-4 | `view_entry` Example 3-7 `print_image_link` Example 3-3 `deliver_media` Example 3-4
Examining image metadata Section 3.1.2.5 | View metadata Figure 3-5 | `view_metadata` Example 3-8 `print_metadata` Example 3-9
Writing new XMP metadata to images Section 3.1.2.6 | Write XMP metadata Figure 3-6 | `write_metadata` Example 3-10
Searching for images that contain specific metadata attributes Section 3.1.2.7 | Search metadata Figure 3-7 | `search_metadata` Example 3-11

You can explore the photo album application using the navigation bar near the top of each Web task page. The leftmost entry of the navigation bar displays the name of the current Web page. On the right, there are links to other Web pages you can access from the current page. Each Web task page contains a link to the View album page, which is the home page for the application.

The photo album application is implemented as a set of PL/SQL procedures and functions organized in a single PL/SQL package. These procedures combine several database features to create the application. Oracle interMedia is used to store and process image data. It is also used to extract metadata from images and embed new metadata into images. The XMLType feature is used to store and process the XML metadata documents. Oracle Text indexes are used to accelerate two kinds of metadata searches. Finally, the PL/SQL Web Toolkit is used to create HTML pages and deliver media content.

For detailed information about these database features, see Oracle XML DB Developer's Guide, Oracle Text Application Developer's Guide, and Oracle Application Server 10g PL/SQL Web Toolkit Reference in the Oracle Application Server 10g Online Documentation Library.

Browsing the Photo Album Using the View album Page The View album page displays thumbnail-size versions of all the images in the photo album as well as a description link positioned under each thumbnail image. When you select a thumbnail image, the full-size image is displayed. When you select the description link for an image, all the metadata for that image is displayed. The View album page is the home page for the application.

Adding Images to the Photo Album Using the Upload photo Page The Upload photo page displays a simple form to collect a description for a new image and the directory path to the location of the image on the local computer. When you click the Upload photo button, the browser sends the image to the Web server and the image is stored in the database.

Searching for Images by Keyword or Phrase Using the Search album Page The Search album page displays a search album form to collect keywords or phrases to initiate full text searches through all image metadata. The application queries the database for all images with metadata that contains the specified keywords or phrases. The search results are displayed as a set of thumbnail images. The search album form is also available from the View album page.

Viewing Full-Size Images Using the View entry Page The View entry page displays the full-size image of a specified photograph, including any description text that was entered for that image when it was uploaded.

Examining Image Metadata Using the View metadata Page The View metadata page displays all the metadata that was extracted from the image when it was uploaded. Up to four types of metadata can be displayed.

Writing New XMP Metadata to Images Using the Write XMP metadata Page The Write XMP metadata page displays a form to collect input for five metadata attributes. These attributes are formatted into an XML document that is embedded within the binary image. The new XMP metadata overwrites any existing XMP metadata.

Searching for Images That Contain Specific Metadata Attributes Using the Search metadata Page The Search metadata page collects input for advanced metadata searches. You can specify the type of metadata to be searched. Optionally, you can also limit the search to a specific XML tag within the specified document. The search results are displayed as a set of thumbnail images.

#### 3.1.2.1 Browsing the Photo Album

The home page for the photo album application, View album, displays the contents of the photo album as thumbnail images in four-column format. Each thumbnail image is also a link to the View entry page. When you click a thumbnail image link, the application displays the full-size image on a View entry page. Included under each thumbnail image on the View album page is the image description that was entered when the image was uploaded to the album. The description is also a link to the View metadata page where all the metadata for this photograph can be examined.

Near the top of the View album page, there is a text entry field (in the shape of a rectangular box) that accepts user input for a full text search through all the photo album metadata. The Search button to the right of the text field initiates the search. The search results are displayed on the Search album page, which is discussed in Section 3.1.2.3.

At the top of the View album page, there is a navigation bar, which includes links to other photo album pages. From the View album page, you can navigate to the Search metadata page or the Upload photo page. These pages are described in Section 3.1.2.7 and Section 3.1.2.2, respectively.

Figure 3-1 shows the View album page for an album that contains five images.

Figure 3-1 View album Page with Five Uploaded Images

Description of the illustration view_album.gif

The PL/SQL procedures `view_album`, `print_album`, `print_image_link`, and `deliver_media` are the primary application components that implement the View album page. The `view_album` procedure is a public procedure that takes a single optional argument. By default, the argument has a `NULL` value. Or, it can have the value of the string entered in the text entry field on the Search album page. When the search argument is `NULL`, the SELECT statement retrieves the `id,``description`, and `thumb` columns for all entries in the `photos` table. When the search string is not `NULL`, the SELECT statement uses the CONTAINS operator to restrict the result set to only images with metadata that matches the search string. (Section 3.1 describes how the application creates a multicolumn text index over the four XMLType columns `PHOTOS.METAIPTC`, `PHOTOS.METAEXIF`, `PHOTOS.METAXMP`, and `PHOTOS.METAORDIMAGE` as well as the `PHOTOS.DESCRIPTION` column.)

Example 3-1 contains some relevant lines of code in the `view_album` procedure.

Example 3-1 Procedure view_album

    --
       -- no search criteria so fetch all entries
       --
       IF search IS NULL THEN
         OPEN album_cur FOR
           SELECT id, description, thumb
           FROM photos
           ORDER BY id;
         print_album( album_cur, 'The photo album is empty.' );
         CLOSE album_cur;
       ELSE
       --    -- use the full-text index to select entries matching the search criteria
       --
         OPEN album_cur FOR
           SELECT id, description, thumb
           FROM photos
           WHERE CONTAINS( description, trim(search) ) > 0
           ORDER BY id;
         print_album( album_cur, 'No photos were found.' );
         CLOSE album_cur;
       END IF;

The SELECT statement is bound to the cursor variable `album_cur` and passed to the procedure `print_album`, which creates the HTML output.

The `print_album` procedure uses the HTP and HTF packages from the PL/SQL Web Toolkit to create the HTML tags that format the output into a four-column table. Each cell in the table contains two links or anchor tags. The first link is to the View entry page, which displays the full-size version of the image. This anchor is implemented by PHOTO_ALBUM.VIEW_ENTRY, and passes `entry_id` as a query string input argument. If the thumbnail image has a nonzero length, then procedure `print_image_link` is called to create an HTML `<img>` tag that is the content (the thumbnail image) of the anchor link. The string `thumb` and the `entry_id` are passed to procedure `print_image_link`, along with the image description, and the height and width of the thumbnail image. These values are used to create the `<img>` tag.

If an image is in a format that interMedia does not support, the application will not be able to create a thumbnail version of the image. In this case, the content of the anchor link is the text view image.

Example 3-2 contains some relevant lines of code in the `print_album` procedure.

Example 3-2 Procedure print_album

    -- escape the description text
           sc_description := htf.escape_sc( entry.description );

           --
           -- Display the thumb-nail image as an anchor tag which can be used
           -- to display the full-size image. If the image format isn't
           -- supported by interMedia, then a thumb-nail wouldn't have been
           -- produced when the image was uploaded, so use the text '[view
           -- image]' instead of the thumb-nail.
           --

           htp.print( '<td headers="c' || colIdx || '" align="center" >
                       <a href="PHOTO_ALBUM.VIEW_ENTRY?entry_id=' ||
                       entry.id || '">' );
           IF entry.thumb.contentLength > 0
           THEN
               print_image_link( 'thumb', entry.id, sc_description,
                                 entry.thumb.height, entry.thumb.width );
           ELSE
               htp.prn( '[view image]' );
           END IF;
           htp.print( '</a>' );

           -- Create link to the metadata
           htp.prn('<br>');
           htp.anchor( curl=>'PHOTO_ALBUM.VIEW_METADATA?entry_id=' || entry.id,
                       ctext=>sc_description );
           htp.prn('</td>');

The procedure `print_image_link` uses the `height` and `width` arguments to populate the `height` and `width` attributes of the `<img>` tag. The `description` argument is used to create text for the `alt` attribute. If the `description` argument is empty, a default string is constructed. Finally, the `src` attribute is set to the URL PHOTO_ALBUM.DELIVER_MEDIA with two query string arguments, `media` and `entry_id`. The `media` argument controls whether the thumbnail or full-size version of the image is delivered. The `entry_id` argument identifies the image to be delivered.

Example 3-3 contains some relevant lines of code in the `print_image_link` procedure.

Example 3-3 Procedure print_image_link

    -- add height and width to tag if non zero
     IF height > 0 AND width > 0 THEN
       attributes := attributes || ' height=' || height || ' width=' || width;
     END IF;

     -- create an alt text if none given
     IF alt IS NULL THEN
       IF type = 'thumb' THEN
         alt2 := 'thumb-nail image ';
       ELSE
         alt2 := 'full-size image ';
       END IF;
       alt2 := alt2 || 'for album entry ' || entry_id;
     ELSE
       alt2 := alt;
     END IF;

     htp.img( curl=>'PHOTO_ALBUM.DELIVER_MEDIA?media=' || type ||
                      ampersand || 'entry_id=' || entry_id,
                calt=>alt2, cattributes=>attributes );

The procedure `deliver_media` fetches the image content from the database. The `If-Modified-Since` HTTP request header is compared to the last modification time of the image. If the image has not been modified, a response is sent that the browser can display the image from its cache. Otherwise, the image MIME type and last modified time are sent to the Web server, along with the image content.

Example 3-4 contains some relevant lines of code in the `deliver_media` procedure.

Example 3-4 Procedure deliver_media

    --
       -- Fetch the thumb-nail or full-size image from the database.
       --
       IF media = 'thumb'
       THEN
           SELECT thumb INTO local_image FROM photos WHERE id = entry_id;
       ELSE
           SELECT image INTO local_image FROM photos WHERE id = entry_id;
       END IF;

       --
       -- Check update time if browser sent If-Modified-Since header
       --
       IF ordplsgwyutil.cache_is_valid( local_image.getUpdateTime() )
       THEN
         owa_util.status_line( ordplsgwyutil.http_status_not_modified );
         RETURN;
       END IF;

       --
       -- Set the MIME type and deliver the image to the browser.
       --
       owa_util.mime_header( local_image.mimeType, FALSE );
       ordplsgwyutil.set_last_modified( local_image.getUpdateTime() );
       owa_util.http_header_close();

       IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) <> 'HEAD' THEN
         wpg_docload.download_file( local_image.source.localData );
       END IF;

#### 3.1.2.2 Adding Images to the Photo Album

The Upload photo page is used to add new images to the photo album. The page displays a form with two text entry fields. In the Description: field, you can optionally enter a word or short phrase that describes the image. In the File name: field, enter the name of the image file or click Browse... to locate the image file to be uploaded. The Upload photo button under the File name: field starts the upload operation. When the image is successfully uploaded, the View album page appears. From that page, you can display the contents of the photo album, as described in Section 3.1.2.1.

At the top of the Upload photo page, there is a navigation bar, which includes links to other photo album pages. From the Upload photo page, you can return to the View album page or select the Search metadata page. These pages are described in Section 3.1.2.1 and Section 3.1.2.7, respectively.

Figure 3-2 shows an Upload photo page with all the entry fields completed.

Figure 3-2 Completed Upload photo Page

Description of the illustration upload_photo.gif

The PL/SQL procedures `view_upload_form`, `print_upload_form`, and `insert_new_photo` are the primary application components that implement the Upload photo page. Together, view_upload_form and print_upload_form create the HTML page that is displayed. The page contains a form tag, a portion of which is shown in Example 3-5. The target of the form is PHOTO_ALBUM.INSERT_NEW_PHOTO.

Example 3-5 contains some relevant lines of code in the `print_upload_form` procedure.

Example 3-5 Procedure print_upload_form

    <form action="PHOTO_ALBUM.INSERT_NEW_PHOTO"
    method="post"
    enctype="multipart/form-data">
    database.

Procedure `insert_new_photo` receives the form, processes the inputs, and stores the new image in the database.

First, the `insert_new_photo` procedure checks that a file name was entered into the upload form. The image size, MIME type, and BLOB locator for the image content are selected from the document upload table, and the size is checked to ensure that the image is not of zero length. If the `description` field is blank, a description is created using the file name.

Next, the ORDSYS.ORDIMAGE.INIT( ) function is called to initialize the `thumb` and `image` ORDImage object type columns with an empty BLOB for the new row to be stored in the `photos` table. A SQL SELECT FOR UPDATE statement fetches the newly initialized thumbnail image and full-size image object type columns for updating. A DBMS_LOB.COPY operation loads the image from the upload table into the `image` ORDImage object type column.

The ORDImage object method setProperties( ) reads the image and sets the image object attributes. Because some browsers cannot display some image formats inline, in this sample application, BMP formatted images are converted to a JPEG image format (for images with more than 8 bits of color), or a GIFF image format (for images with less than 9 bits of color) by calling the `get_preferred_format` function. A processCopy( ) operation is performed on the full-size image to create the thumbnail image.

The ORDImage object getMetadata( ) method is called to extract all supported types of image metadata. The root element of each XML document in the return vector is examined to discover the metadata type so that the documents can be stored in the correct columns.

Then, a SQL UPDATE statement stores the full-size image, the thumbnail image, and the image metadata documents in the database. Procedure `sync_indexes` is called to force an update of the text indexes. Finally, the form data input is deleted from the document upload table. A success message is returned to the browser, and the browser is redirected to the View album page.

Example 3-6 contains some relevant lines of code in the `insert_new_photo` procedure.

Example 3-6 Procedure insert_new_photo

    --
       -- Make sure a file name has been provided. If not, display an error
       -- message, then re-display the form.
       --
       IF new_photo IS NULL OR LENGTH( new_photo ) = 0
       THEN
           print_page_header;
           print_error( 'Please supply a file name.' );
           print_upload_form;
           print_page_trailer( TRUE );
           return;
       END IF;

       --
       -- Get the length, MIME type and the BLOB of the new photo from the
       -- upload table.
       --
       SELECT doc_size,
              mime_type,
              blob_content
       INTO   upload_size,
              upload_mime_type,
              upload_blob
       FROM photos_upload
       WHERE name = new_photo;

       --
       -- Make sure we have a valid file.
       --
       IF upload_size = 0
       THEN
           print_page_header;
           print_heading( 'Error message' );
           htp.print( '<hr size="-1"><p>Please supply a valid image file.</p>' );
           print_upload_form;
           print_page_trailer( TRUE );
           return;
       END IF;

       --
       -- If the description is blank, then use the file name.
       --
       IF c_description IS NULL
       THEN
           c_description := new_photo;
           pos := INSTR( c_description, '/', -1 );
           IF pos > 0
           THEN
               c_description := SUBSTR( c_description, pos + 1 );
           END IF;
           c_description := SUBSTR( 'Image from file: ' ||
                            c_description || '.', 1, 40 );
       END IF;
       --
       -- Insert a new row into the table, returning the newly allocated sequence
       -- number.
       INSERT INTO photos ( id, description, metaExif, metaIPTC, metaXMP,
                            image, thumb )
       VALUES ( photos_sequence.nextval, c_description, NULL, NULL, NULL,
                    ORDSYS.ORDIMAGE.INIT(), ORDSYS.ORDIMAGE.INIT() )
       RETURN id
       INTO new_id;

       --
       -- Fetch the newly initialized full-size and thumb-nail image objects.
       --
       SELECT image,
              thumb
       INTO new_image,
            new_thumb
       FROM photos
       WHERE id = new_id
       FOR UPDATE;

       --
       -- Load the photo from the upload table into the image object.
       --
       DBMS_LOB.COPY( new_image.source.localData, upload_blob, upload_size );
       new_image.setLocal();
       --
       -- Set the properties. If the image format is not recognized, then
       -- the exception handler will set the MIME type and length from the
       -- upload table.
       --
       BEGIN
           new_image.setProperties();
       EXCEPTION
           WHEN OTHERS THEN
                new_image.contentLength := upload_size;
                new_image.mimeType := upload_mime_type;
       END;

       --
       -- Some image formats are supported by interMedia but may not be able
       -- to be displayed in-line by a browser. The BMP format is one example.
       -- Convert the image to a GIF or JPEG based on number of colors in the
       -- image.
       --
       IF new_image.contentFormat IS NOT NULL AND
          ( new_image.mimeType = 'image/bmp' OR
            new_image.mimeType = 'image/x-bmp' )
       THEN
           BEGIN
               new_image.process(
                            'fileFormat=' ||
                            get_preferred_format( new_image.contentFormat ) );
           EXCEPTION
               WHEN OTHERS THEN
                   NULL;
           END;
       END IF;

       --
       -- Try to copy the full-size image and process it to create the thumb-nail.
       -- This may not be possible if the image format is not recognized.
       --
       BEGIN
           new_image.processCopy( thumb_scale, new_thumb );
       EXCEPTION
           WHEN OTHERS THEN
               new_thumb.deleteContent();
               new_thumb.contentLength := 0;
       END;
      --
       -- fetch the metadata and sort the results
       --
       BEGIN
         metav := new_image.getMetadata( 'ALL' );
         FOR i IN 1..metav.count() LOOP
           meta_root := metav(i).getRootElement();
           CASE meta_root
             WHEN 'ordImageAttributes' THEN xmlORD := metav(i);
             WHEN 'xmpMetadata'  THEN xmlXMP  := metav(i);
             WHEN 'iptcMetadata' THEN xmlIPTC := metav(i);
             WHEN 'exifMetadata' THEN xmlEXIF := metav(i);
             ELSE NULL;
           END CASE;
         END LOOP;
       EXCEPTION
         WHEN OTHERS THEN
           NULL;
       END;

       --
       -- Update the full-size and thumb-nail images in the database.
       -- Update metadata columns
       --
       UPDATE photos
       SET image = new_image,
           thumb = new_thumb,
           metaORDImage = xmlORD,
           metaEXIF = xmlEXIF,
           metaIPTC = xmlIPTC,
           metaXMP = xmlXMP
       WHERE id = new_id;

       --    -- update the text indexes
       --    sync_indexes;

       --
       -- Delete the row from the upload table.
       --
       DELETE FROM photos_upload WHERE name = new_photo;
       COMMIT;

       --
       -- Redirect browser to display full album.
       --    print_page_header(
           '<meta http-equiv="refresh" content="2;url=PHOTO_ALBUM.VIEW_ALBUM">' );
       print_heading( 'Photo successfully uploaded into photo album' );

#### 3.1.2.3 Searching for Images by Keyword or Phrase

You can use the View album and Search album pages to perform a keyword or phrase search of the metadata stored in the photo album. On either of these pages, enter the keyword or phrase in the Full text search: text entry field and click Search. The photo album application uses the `CONTEXT` text index to locate images that have metadata containing the text you entered. If the search is successful, the thumbnail versions of the matching images are displayed in a four-column table. Select the thumbnail image to view the full-size version, or select the description link below the thumbnail image to view the metadata for the image. If the search fails, the message "No photos were found" is displayed.

At the top of the Search album page, there is a navigation bar, which includes links to other photo album pages. From the Search album page, you can return to the View album page or select the Search metadata or Upload photo pages. These pages are described in Section 3.1.2.1, Section 3.1.2.7, and Section 3.1.2.2, respectively.

Figure 3-3 shows a Search album page that contains the results of a successful search operation.

Figure 3-3 Search album Page Showing Results

Description of the illustration search_album.gif

Full text searching of the photo album is implemented by the `view_album` and `print_album` procedures. See Section 3.1.2.1 for a discussion of these procedures.

#### 3.1.2.4 Viewing Full-Size Images

When you select a thumbnail image, the application directs you to the View entry page. This page displays the description of the image and the full-size version of the image.

At the top of the View entry page, there is a navigation bar, which includes links to other photo album pages. From the View entry page, you can return to the View album page, or select any of the View metadata, Write metadata, Search metadata, or Upload photo pages. These pages are described in Section 3.1.2.1, Section 3.1.2.5, Section 3.1.2.6, Section 3.1.2.7, and Section 3.1.2.2, respectively.

Figure 3-4 shows a View entry page that contains the description and the full-size version of an image.

Figure 3-4 View entry Page with a Full-Size Image

Description of the illustration view_entry.gif

The PL/SQL procedures `view_entry`, `print_image_link`, and `deliver_media` are the primary application components that implement the View entry page. The procedure `view_entry` takes a single parameter, `entry_id`, which uniquely locates the image in the `photos` table. The description and image object are fetched from the `photos` table. The procedure `print_image_link` creates the HTML `<img>` tag, and then calls procedure `deliver_media` to fetch the image content. See Section 3.1.2.1 for more information about the `print_image_link` and `deliver_media` procedures.

Example 3-7 contains some relevant lines of code in the `view_entry` procedure.

Example 3-7 Procedure view_entry

    --
       -- Fetch the row.
       --
       BEGIN
         SELECT htf.escape_sc(description), image
         INTO sc_description, photo
         FROM photos
         WHERE id = entry_id;
         EXCEPTION
         WHEN no_data_found THEN
           print_error( 'Image <b>' || htf.escape_sc(entry_id) ||
                        '</b> was not found.</p>' );
           print_page_trailer( TRUE );
           return;
       END;

       print_image_link( 'image', entry_id, sc_description,                      photo.height, photo.width );

#### 3.1.2.5 Examining Image Metadata

You can use the View metadata page to examine all the metadata for a specific image. Typically, you access this page from the View album page by selecting the description link below a thumbnail image. You can also access this page by selecting the View metadata link from the navigation bar. The View metadata page displays the thumbnail version of the image. To the right of the thumbnail image, there is a list of the metadata documents for this image. Each entry in the list is a link that takes you to the metadata document on the View metadata page.

At the top of the View metadata page, there is a navigation bar, which includes links to other photo album pages. From the View metadata page, you can return to the View album page, or select any of the View entry, Write metadata, Search metadata, or Upload photo pages. These pages are described in Section 3.1.2.1, Section 3.1.2.4, Section 3.1.2.6, Section 3.1.2.7, and Section 3.1.2.2, respectively.

Figure 3-5 shows a View metadata page that contains two types of metadata (XMP and ORDIMAGE) for an image.

Figure 3-5 View metadata Page with Metadata for an Uploaded Image

Description of the illustration view_metadata.gif

The PL/SQL procedures `view_metadata` and `print_metadata` are the primary application components that implement the View metadata page. The procedure `view_metadata` is passed the argument `entry_id`, which uniquely identifies the image in the `photos` table. A SELECT statement retrieves all the `XMLtype` metadata columns for the specified entry. If the metadata column is not `NULL`, procedure `print_metadata` is called to display the XML document inside an HTML `<pre>` tag.

Example 3-8 contains some relevant lines of code in the `view_metadata` procedure.

Example 3-8 Procedure view_metadata

    --
       -- Fetch the row.
       --
       SELECT metaOrdImage, metaEXIF, metaIPTC, metaXMP
       INTO   metaO, metaE, metaI, metaX
       FROM   photos
       WHERE  id = entry_id;

       -- display the EXIF metadata
       IF metaE IS NOT NULL THEN
         htp.print( '<span class="bigBlue" id="exifMetadata">EXIF</span>' );
         htp.print( '<br><pre>' );
         print_metadata( metaE );      htp.print( '</pre>' );
       END IF;

The `print_metadata` procedure accepts an XMLType document as an argument. It uses the getClobVal( ) method to access the document as a CLOB. The content of the CLOB is read in a loop and formatted in the HTML page using the `htp.prints` procedure. The `htp.prints` procedure escapes the '<' and '>' characters so that they are rendered properly by the Web browser.

Example 3-9 contains some relevant lines of code in the `print_metadata` procedure.

Example 3-9 Procedure print_metadata

    metaClob := meta.getClobVal();
       len := dbms_lob.getLength( metaClob );
       IF bufSize > len THEN
         bufSize := len;
       END IF;
       WHILE len > 0 LOOP
         dbms_lob.read( metaClob, bufSize, pos, buf );
         htp.prints( buf );
         pos := pos + bufSize;
         len := len - bufSize;
       END LOOP;

#### 3.1.2.6 Writing New XMP Metadata to Images

You can use the Write XMP metadata page to write new or replace existing XMP metadata in an image. interMedia provides support for writing XMP metadata only. You can access the Write XMP metadata page by selecting the Write metadata link in the navigation bar from either the View entry page or the View metadata page.

The Write XMP metadata page displays the thumbnail version of the image to be modified. The page also displays an input form to collect metadata attributes in these five text entry fields:

- Title: Specify a title for the photograph.

- Creator: Enter the name of the person who took the photograph. This field is optional.

- Date: Enter the date the photograph was taken. This field is optional.

- Description: Enter a description, such as the subject of the photograph. This field is optional.

- Copyright: Enter the month and year when the photograph was taken. This field is optional.

Click Write it! to send the form to the application and embed the metadata in XMP format in the image.

At the top of the Write XMP metadata page, there is a navigation bar, which includes links to other photo album pages. From the Write XMP metadata page, you can return to the View album page, or select any of the View entry, View metadata, Search metadata, or Upload photo pages. These pages are described in Section 3.1.2.1, Section 3.1.2.4, Section 3.1.2.5, Section 3.1.2.7, and Section 3.1.2.2, respectively.

Figure 3-6 shows a Write XMP metadata page with completed entries for an image.

Figure 3-6 Completed Write XMP metadata Page with XMP Metadata for an Uploaded Image

Description of the illustration write_metadata.gif

The PL/SQL procedure `write_metadata` receives the form input fields from the browser. The procedure creates an XML document (as a string buffer) that is valid to the interMedia XMP schema `http://xmlns.oracle.com/ord/meta/xmp`. The string buffer is used to create an XMLType object.

A SELECT FOR UPDATE statement retrieves the image to be modified. The interMedia method putMetadata( ) is called to embed the XML document into the image. The modified image is stored back to the photos table. Finally, procedure `sync_indexes` is called to update the text indexes.

Example 3-10 contains some relevant lines of code in the `write_metadata` procedure.

Example 3-10 Procedure write_metadata

    -- Create the XMP packet it must be schema valid
       -- to "http://xmlns.oracle.com/ord/meta/xmp"
       -- and contain an <RDF> element. This example uses
       -- the Dublin Core schema as implemented by Adobe XMP
       buf := '<xmpMetadata xmlns="http://xmlns.oracle.com/ord/meta/xmp"
                xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/xmp
                http://xmlns.oracle.com/ord/meta/xmp"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
         <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">
         <rdf:Description about="" xmlns:dc="http://purl.org/dc/elements/1.1/">
         <dc:title>' || htf.escape_sc(title) || '</dc:title>';

       IF c_creator IS NOT NULL THEN
         buf := buf || '<dc:creator>' || htf.escape_sc(c_creator)
                    || '</dc:creator>';
       END IF;
       IF c_date IS NOT NULL THEN
         buf := buf || '<dc:date>' || htf.escape_sc(c_date)
                    || '</dc:date>';
       END IF;
       IF c_description IS NOT NULL THEN
         buf := buf || '<dc:description>' || htf.escape_sc(c_description)
                    || '</dc:description>';
       END IF;
       IF c_copyright IS NOT NULL THEN
         buf := buf || '<dc:copyright>' || htf.escape_sc(c_copyright)
                    || '</dc:copyright>';
       END IF;
       buf := buf || '
         </rdf:Description>
         </rdf:RDF>
         </xmpMetadata>';

       xmp := XMLType.createXML(buf, 'http://xmlns.oracle.com/ord/meta/xmp');

       --    -- select image for update
       -- description is selected to force update of CTX index
       --
       SELECT image, description
       INTO img, des
       FROM photos
       WHERE id = entry_id
       FOR UPDATE;

       --
       -- write the metadata
       --
       img.putMetadata( xmp, 'XMP' );

       --
       -- save updated image and new metadata to table
       -- description updated to force update of CTX index
       --
       UPDATE photos
       SET image = img,
           metaXMP = xmp,
           description = des
       WHERE id = entry_id;

       -- update the text indexes
       sync_indexes;

The input data shown in Example 3-10 would result in the storage of the following metadata in the image:

    <xmpMetadata xmlns="http://xmlns.oracle.com/ord/meta/xmp"
                xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/xmp
                http://xmlns.oracle.com/ord/meta/xmp"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">
       <rdf:Description about="" xmlns:dc="http://purl.org/dc/elements/1.1/">
         <dc:title>Story time</dc:title>
         <dc:creator>father</dc:creator>
         <dc:date>July 4, 2001</dc:date>
         <dc:description>family reading</dc:description>
         <dc:copyright>mother</dc:copyright>
       </rdf:Description>
     </rdf:RDF>
    </xmpMetadata>

#### 3.1.2.7 Searching for Images That Contain Specific Metadata Attributes

You can use the Search metadata page to search a specific metadata type as well as to limit your search to a specific tag within a metadata document. You can access the Search metadata page by selecting the Search metadata link in the navigation bar of any photo album application Web page.

The Search metadata page displays a form with four fields to define how the search is to be performed. Use the pull-down menu in the Search in metadata: field to select the type of metadata (EXIF, IPTC, or XMP) to be searched. When this field is changed, the fields Search in tag: and Search method: are initialized with values that are appropriate to the type of metadata search.

Use the drop-down list in the Search in tag: field to limit the search to a specific XML element within a metadata document. The list is populated with element names that are appropriate for the selected metadata type. When the value --Any tag-- is showing, the search will look at all elements within the document type. When the XMP metadata type is selected, searches are limited to Description elements within the parent RDF element. If the metadata document is properly constructed, selecting RDF/Description in this field should search all relevant metadata within XMP documents.

In the Search method: field, select the radio button Contains to specify a search where an element contains the search string. Select the radio button Equals to specify a search where element values are matched exactly to the search string. For searches in XMP metadata, only the Contains search method is available.

Finally, enter a keyword or phrase in the Search string: field and click Search. If the search is successful, the thumbnail versions of the matching images are displayed in a four-column table. Select the thumbnail image to view the full-size version of an image. Or, select the description link below the thumbnail image to view the metadata for the image. If the search fails, the message "No photos matched the search criteria." is displayed.

At the top of the Search metadata page, there is a navigation bar, which includes links to other photo album pages. From the Search metadata page, you can return to the View album page or select the Upload photo page. These pages are described in Section 3.1.2.1 and Section 3.1.2.2, respectively.

Figure 3-7 shows a Search metadata page that contains sample search criteria and results from a successful search operation.

Figure 3-7 Completed Search metadata Page for an Uploaded Image

Description of the illustration search_metadata.gif

The PL/SQL procedure `search_metadata` receives the form input fields from the Web browser. The search parameters are used to build a query to find images that contain the desired metadata. The search is accomplished using the SQL EXISTSNODE operator. The EXISTSNODE operator is used to search an XML document for content that matches the given search predicate. The operator returns `1` if the document matched the search, and `0` otherwise. The operator takes three arguments. The first argument is the name of the XMLType column. In this application, the column to search is determined by the metadata type selected in the pull-down menu for the Search in metadata: field. The second argument is an XPATH expression that specifies the content to be searched and how the matching is evaluated. The Search in tag: and Search method: fields are used to build the XPATH expression. The third argument is a string to define the XML namespaces to be used to process the document and the XPATH expression.

For example, assume that the `search_metadata` procedure receives input that specifies to search the `caption` tag in IPTC metadata for an exact match of the word "farm". The query to accomplish this search is as follows:

    SELECT id, description, thumb
     FROM photos
     WHERE EXISTSNODE( metaIptc,
                      '/iptcMetadata//caption="farm"',
                      'xmlns:ora="http://xmlns.oracle.com/xdb" ' ||
                      'xmlns="http://xmlns.oracle.com/ord/meta/iptc"' ) = 1;

The second argument to the EXISTSNODE operator, `'/iptcMetadata//caption="farm"`, specifies a search for all `<caption>` elements under the root element `<iptcMetadata>` where the `<caption>` content is equal to `"farm"`.

For more information about the EXISTSNODE operator, see Oracle XML DB Developer's Guide. For more information about the contains( ) text search operator, see Oracle Text Application Developer's Guide.

Example 3-11 contains some relevant lines of code in the `search_metadata` procedure.

Example 3-11 Procedure search_metadata

    -- set up search variables for EXIF documents
       IF mtype = 'exif' THEN
         IF op = 'equals' THEN
           xpath  := '/exifMetadata//' || tag || '="' || c_search || '"';
         ELSE  -- default to contains
           xpath  := '/exifMetadata//' || tag ||
                     '[ora:contains(text(), "' || c_search || '")>0]';
         END IF;

         nspace := 'xmlns:ora="http://xmlns.oracle.com/xdb" ' ||
                   'xmlns="http://xmlns.oracle.com/ord/meta/exif"';

         OPEN album_cur FOR
           SELECT id, description, thumb
           FROM photos
           WHERE existsnode( metaExif, xpath, nspace ) = 1;

       -- set up search variables for IPTC documents
       ELSIF mtype = 'iptc' THEN
         IF op = 'equals' THEN
           xpath  := '/iptcMetadata//' || tag || '="' || c_search || '"';
         ELSE  -- default to contains
           xpath  := '/iptcMetadata//' || tag ||
                     '[ora:contains(text(), "' || c_search || '")>0]';
         END IF;

         nspace := 'xmlns:ora="http://xmlns.oracle.com/xdb" ' ||
                   'xmlns="http://xmlns.oracle.com/ord/meta/iptc"';

         OPEN album_cur FOR
           SELECT id, description, thumb
           FROM photos
           WHERE existsnode( metaIptc, xpath, nspace ) = 1;

       -- set up search variables for XMP documents
       ELSIF mtype = 'xmp' THEN
         -- default to contains
        xpath  := '/xmpMetadata/rdf:RDF/rdf:Description/*[ora:contains(text(), "'
                   || c_search || '")>0]';

         -- add rdf namespace prefix
         nspace := 'xmlns:ora="http://xmlns.oracle.com/xdb" ' ||
                   'xmlns="http://xmlns.oracle.com/ord/meta/xmp" ' ||
                   'xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"';

         OPEN album_cur FOR
           SELECT id, description, thumb
           FROM photos
           WHERE existsnode( metaXMP, xpath, nspace ) = 1;

       ELSE
         errorMsg := 'Search domain is invalid: ' || htf.escape_sc(mtype);
       END IF;

      print_search_form( mtype, tag, op, c_search );
       htp.print('<hr size="-1">');
       print_album( album_cur, 'No photos matched the search criteria.' );

## 3.2 Other Photo Album Sample Applications

This set of interMedia photo album sample applications demonstrates the use of the interMedia image object type to upload and retrieve media data stored in Oracle Database.

Each of these photo album applications, when installed, creates a table named `photos` and a sequence named `photos_sequence`.

The `photos` table is described by the following CREATE TABLE statement:

    CREATE TABLE photos(id          NUMBER PRIMARY KEY,
                        description VARCHAR2(40) NOT NULL,
                        location    VARCHAR2(40),
                        image       ORDSYS.ORDIMAGE,
                        thumb       ORDSYS.ORDIMAGE);

Note that the data type for the `image` and `thumb` columns are defined as interMedia image object types to store the full-size images and the generated thumbnail images.

The `photos_sequence` sequence is defined by the following CREATE SEQUENCE statement:

    CREATE SEQUENCE photos_sequence;

### 3.2.1 Oracle interMedia Java Servlet Photo Album Sample Application

The interMedia Java Servlet Photo Album sample application demonstrates the use of Oracle interMedia Java Classes for Servlets and JSP to upload and retrieve multimedia data to and from the database. Users access the servlet application to view the contents of the photo album, including thumbnail versions of each photograph, to view the full-size version of any photograph, and to upload new photographs into the album.

#### 3.2.1.1 Running the Java Servlet Photo Album Application

After you have completed the setup tasks and have built the Java servlet photo album application, you can run the application by entering the following URL in the address field of your Web browser:

- Default installation of Oracle Application Server or Oracle Database

      <protocol><hostname:port-number>/servlet/PhotoAlbumServlet

- Default installation of Tomcat 3.2 on Windows

      <protocol><hostname:port-number>/examples/servlet/PhotoAlbumServlet

  The `<protocol>` field is `http://`, and the `<hostname:port-number>` field is the host name and port number of the system where your HTTP server is running.

When first invoked, the photo album application displays any images that are currently stored in the album. By default, the photo album is empty when first installed. To upload a new photograph, select Upload new photo. Enter a description of the photograph, the location where the photograph was taken, and the name of the image file (or browse to its directory location), then click Upload photo. The contents of the photo album are displayed along with a picture of the new photograph. Click the thumbnail image to view the full-size version of the photograph.

When the photo album application displays the text view image instead of its thumbnail image, the image format that was uploaded was not recognized by interMedia. Click view image to display the full-size image.

You can now begin to load your photo album application with your favorite photographs.

#### 3.2.1.2 Description of the interMedia Java Servlet Photo Album Application

The interMedia Java Servlet Photo Album application combines both business logic and the presentation into a single servlet, which when compiled, creates two class files, `PhotoAlbumServlet.class` and `PhotoAlbumRequest.class`.

To follow along with the description of tasks, users should refer to a copy of the `PhotoAlbumServlet.java` file, which can be found in:

`<ORACLE_HOME>``/ord/http/demo/servlet` (on UNIX)

`<ORACLE_HOME>``\ord\http\demo\servlet` (on Windows)

PhotoAlbumServlet Class

The `PhotoAlbumServlet` class performs the following tasks:

- Extends the HttpServlet and contains the user-entered connection information.

      public class PhotoAlbumServlet extends HttpServlet

- Accepts connection information by allowing you to select the connection method, supply the necessary connection information, and optionally change the user name and password to connect to a schema other than `scott/tiger`.

      private final static String JDBC_CONNECT_STRING =
      //  "jdbc:oracle:oci:@<SQL*Net TNS name>";   // 9i JDBC OCI driver
      //  "jdbc:oracle:oci8:@<SQL*Net TNS name>";   // 8i JDBC OCI driver
          "jdbc:oracle:thin:@<host>:<port>:<sid>";  // JDBC Thin driver

      private final static String JDBC_USER_NAME = "scott";
      private final static String JDBC_PASSWORD  = "tiger";

- Instantiates a Java stack used to implement a simple connection-pooling mechanism.

      private static Stack connStack = new Stack();

- Defines a flag to indicate whether or not the JDBC Thin driver has been loaded.

      private static boolean driverLoaded = false;

- Defines a servlet initialization method.

      public void init( ServletConfig config ) throws ServletException
      {
          super.init(config);
      }

- Defines a doGet( ) method to process an HTTP GET request containing an HttpServletRequest object and HttpServletResponse object, and instantiates a PhotoAlbumRequest object to process the request to deliver either a full-size or thumbnail image to the browser, or to display an upload form or the contents of the photo album as thumbnail images.

      public void doGet( HttpServletRequest request,
                         HttpServletResponse response )
          throws ServletException, IOException
      {
          Connection conn = null;

          //
          // Use a try-block to ensure that JDBC connections are always returned
          // to the pool.
          //
          try
          {
              //
              // Get a JDBC connection from the pool.
              //
              conn = getConnection();

              //
              // Instantiate a PhotoAlbumRequest object to process the request.
              //
              PhotoAlbumRequest albumRequest =
                  new PhotoAlbumRequest( conn, request, response );

              //
              // Figure out what to do based on query string parameters.
              //
              String view_media = request.getParameter( "view_media" );
              if ( view_media != null )
              {
                  //
                  // Deliver a full-sized or thumbnail image to the browser.
                  //
                  albumRequest.viewMedia( view_media );
                  return;
              }
              else if ( request.getParameter( "view_form" ) != null )
              {
                  //
                  // Display the HTML upload form.
                  //
                  albumRequest.viewUploadForm();
              }
              else if ( request.getParameter( "view_entry" ) != null )
              {
                  //
                  // Display full-sized photo image.
                  //
                  albumRequest.viewPhoto();
              }
              else
              {
                  //
                  // Display album contents with thumbnail images by default.
                  //
                  albumRequest.viewAlbum();
              }
          }
          catch ( SQLException e )
          {
              //
              // Log what went wrong.
              //
              e.printStackTrace( System.out );

              //
              // Turn SQL exceptions into ServletExceptions.
              //
              throw new ServletException( e.toString() );
          }
          finally
          {
              //
              // If we have a JDBC connection, then return it to the pool.
              //
              freeConnection( conn );
          }
      }

- Defines a doPost( ) method to process an HTTP POST request used to upload a new photograph into the album by instantiating a PhotoAlbumRequest object to process the request and then calling the insertNewPhoto( ) method.

      public void doPost( HttpServletRequest request,
                          HttpServletResponse response )
          throws ServletException, IOException
      {
          Connection conn = null;

          //
          // Use a try-block to ensure that JDBC connections are always returned
          // to the pool.
          //
          try
          {
              //
              // Get a JDBC connection from the pool.
              //
              conn = getConnection();

              //        // Instantiate a PhotoAlbumRequest object to process the request.
              //
              PhotoAlbumRequest albumRequest =
                  new PhotoAlbumRequest( conn, request, response );

              //
              // Insert the photo into the album.
              //
              albumRequest.insertNewPhoto();
          }
          catch ( SQLException e )
          {
              //
              // Log what went wrong.
              //        e.printStackTrace( System.out );

              //
              // Turn SQL exceptions into ServletExceptions.
              //
              throw new ServletException( e.toString() );
          }
          finally
          {
              //
              // If we have a JDBC connection, then return it to the pool.
              //
              freeConnection( conn );
          }
      }

- Defines a getConnection( ) method.

      private Connection getConnection()
          throws SQLException
      {
          OracleConnection conn = null;

          //
          // Synchronize on the stack object. Load the JDBC driver if not yet
          // done. If there's a free connection on the stack, then pop it off
          // the stack and return it to the caller. Otherwise, create a new
          // connection object and call the version compatibility initialization
          // method.
          //
          synchronized( connStack )
          {
              if ( !driverLoaded )
              {
                  DriverManager.registerDriver(
                                      new oracle.jdbc.driver.OracleDriver() );
                  driverLoaded = true;
              }
              if ( connStack.empty() )
              {
                  conn = (OracleConnection)DriverManager.getConnection
                      ( JDBC_CONNECT_STRING, JDBC_USER_NAME, JDBC_PASSWORD );
              }
              else
              {
                  conn = (OracleConnection)connStack.pop();
              }
          }

          //
          // Enable auto-commit by default.
          //
          conn.setAutoCommit( true );

          return conn;
      }

- Defines a freeConnection( ) method.

      private void freeConnection( Connection conn )
      {
          //
          // Synchronize on the stack object, then push the connection onto the
          // stack.
          //
          if ( conn != null )
          {
              synchronized( connStack )
              {
                  connStack.push( conn );
              }
          }
      }

In summary, the `PhotoAlbumServlet` class responds to the HTTP GET and POST requests by allocating a JDBC connection from a connection pool. Each HTTP GET or POST request is assigned its own JDBC connection from the pool to ensure that multiple requests can be serviced concurrently. An HTTP GET request is used to retrieve image data from the photo album and an HTTP POST request is used to upload image data into the photo album. Then, an instance of the `PhotoAlbumRequest` class is created to execute the request, executes the request, then releases the JDBC connection back to the pool at the end of the request.

PhotoAlbumRequest Class

The `PhotoAlbumRequest` class does the actual processing of an HTTP GET or POST request, and defines the getPreferredFormat( ) function and the following methods:

viewAlbum( )
viewPhoto( )
viewMedia( )
viewUploadForm( )
insertNewPhoto( )
printPageHeader( )
printPageTrailer( )
printMessage( )
printHeading( )
printLink( )

In the viewMedia( ) and insertNewPhoto( ) methods, three objects, OrdHttpResponseHandler, OrdHttpUploadFormData, and OrdHttpUploadFile, are instantiated. These objects are used to call the methods of the respective `OrdHttpResponseHandler`, `OrdHttpUploadFormData`, `OrdHttpUploadFile` classes of the Oracle interMedia Java Classes for Servlets and JSP. For example, in the viewMedia( ) method, the OrdHttpResponseHandler object is instantiated and used to call the sendImage( ) method as shown in the following code:

    OrdHttpResponseHandler handler =
        new OrdHttpResponseHandler( request, response );
    handler.sendImage( img );

The viewAlbum( ), viewPhoto( ), viewMedia( ), and insertNewPhoto( ) methods use the ORAData (formerly getCustomDatum) and ORADataFactory (formerly getFactory) interfaces supplied by Oracle to get the image or thumbnail OrdImage object from the result set to obtain height and width information, to retrieve an image from an OrdImage Java object and deliver it to the browser, and to upload an image in an OrdImage Java object and to also update it in the `photos` table. For example, the following code excerpt is from the viewAlbum( ) method:

    OrdImage img =
        (OrdImage)rset.getORAData( 4, OrdImage.getORADataFactory() );
    .
    .
    .
    out.print( "<td headers=\"image\"><a href=\"" + servletUri +
               "?view_entry=yes&id=" + id + "\">" );
    if ( img.getContentLength() > 0 )
    {
        if (img.getMimeType().startsWith("image/"))
        {
    out.print( "<img src=\"" + servletUri +
                               "?view_media=thumb&id=" + id + "\"" +
                               " height=" + img.getHeight() +
                               " width=" + img.getWidth() +
                               " alt=\"" + description + "\"" +
             " border=1>" );
        }
    }
    else
    {
        out.print( "[view image]" );
    }
    out.println( "</a></td>" );
    out.println( "</tr>" );

What follows is a more detailed description of each method and what it does:

- The viewAlbum( ) method does the following:

  - Initializes the row count to zero.

  - Writes a common page header on the HTML page using the function printPageHeader( ).

  - Executes a SELECT statement to fetch all the thumbnail images in the photo album, order them by description, and display the description and location information for each along with the thumbnail image if it exists, and returns the results in a result set.

  - Displays the thumbnail images in an HTML table with column headers labeled `Description`, `Location`, and `Image`.

  - Within a `while` block, reads the contents of the result set by reading the first row's contents beginning with the `id` value, displays the description and location values, then gets the thumbnail OrdImage object and builds the height and width attributes for each thumbnail image.

  - Displays the thumbnail image using an HTML anchor tag that can be used to display the full-size image. When a user clicks the thumbnail image or view image, the full-size image is displayed.

  - Displays the contents of the photo album within an HTML anchor tag using the tag `<IMG SRC="<servlet-path>?view_media=thumb&id=...">` to display the thumbnail images, where `<servlet-path>` is the value of `servletUri`. If the thumbnail image was not created because the image format was not supported by interMedia, the text view image is displayed instead.

  - Increments the row count to see if the photo album is empty; if so, it displays the message "The photo album is empty".

  - Displays an HTML anchor tag near the bottom of the HTML page using the printLink( ) function with the text Upload new photo.

  - Writes a common trailer at the bottom of the HTML page by calling the printPageHeader( ) function, however, in this case, sets the Boolean argument to false to not display the common page trailer.

  - Closes the result set and the statement.

- The viewPhoto( ) method displays the full-size version of a photograph and does the following:

  - Writes a common page header on the HTML page using the function printPageHeader( ).

  - Gets the value of the `id` column for the entry being viewed.

  - Executes a SQL SELECT statement to fetch the entry's description, location, and full-size image where the value of `id` in the `where` clause is a parameter marker and returns the results in a result set.

  - Gets the image OrdImage object from the result set in order to later build the image height and width attributes within the `<IMG SRC=...>` image tag.

  - Displays the full-size image in an HTML table beginning with the column names `Description` and `Location`, and displays the entry's values for these two columns.

  - Builds the URL to fetch a full-size image for this entry by using an image tag `<IMG SRC="<servlet-path>?view_media=image&id=...">` to display an image in the column labeled `Photo`, where `<servlet-path>` is the value of `servletUri`.

  - Displays the full-size images height and width by calling the getHeight( ) and getWidth( ) interMedia object methods. If the image format is not recognized by interMedia, height and width values will be zero and will not be displayed.

  - Writes a common page trailer at the bottom of the HTML page by calling the printPageHeader( ) function and setting its Boolean argument to true to display the common page trailer.

  - Closes the result set and the statement.

- The viewMedia( ) method is invoked by both thumbnail and full-size image URLs to retrieve a thumbnail or full-size image from the `photos` table and deliver it to the browser using the `OrdHttpResponseHandler` class. This method does the following:

  - Executes a SQL SELECT statement to fetch either the thumbnail or full-size image where the value of `id` in the `where` clause is a parameter marker and returns the results in a result set. The SQL SELECT statement is built dynamically with the string `media` equating to either the thumbnail image column or the full-size image column.

  - Fetches a row from the result set.

  - Gets the OrdImage object from the result set.

  - Uses the `OrdHttpResponseHandler` class to create an OrdHttpResponseHandler object to retrieve the image from the OrdImage object and deliver it to the browser using the sendImage( ) method. The sendImage( ) method supports browser content caching by supporting the If-Modified-Since and Last-Modified headers.

  - Closes the result set and the statement.

- The viewUploadForm( ) method displays an HTML form that allows users to upload new photographs and does the following:

  - Calls the printPageHeader( ) function to produce the common page header.

  - Defines the form action as a multipart/form-data POST request.

  - Calls the `upload_form_fields` static string containing the contents of the upload form. The upload form is defined as a table with rows labeled `Description` and `Location`, with an input type of text and named description and location respectively, followed by a row labeled `File name:`, with an input type of `file` and named `photo`, and finally a row with no label, an input type of `submit`, and a value of `Upload photo`.

  - Calls the printPageTrailer( ) function to produce the common page trailer.

- The insertNewPhoto( ) method does the following:

  - Uses the `OrdHttpUploadFormData` class to parse a multipart/form-data POST request containing an uploaded photograph.

  - Uses the `OrdHttpUploadFile` class to upload the new photograph into the database.

  - Executes a SQL SELECT photos_sequence.nextval statement to get the next value of the `id` column for the new row to be inserted into the `photos` table.

  - Executes a SQL INSERT statement to insert a new row in the `photos` table.

  - Executes a SQL SELECT...FOR UPDATE statement to fetch the initialized full-size and thumbnail image objects from the `photos` table.

  - Calls the loadImage( ) method in the `OrdHttpUploadFile` class to populate an OrdImage object named `image` with the full-size image and sets the properties or attribute values of the image object based on the image contents.

  - Checks to see what the image format is and if it is an image format that may not be displayed inline by a browser, such as a BMP image format, then calls the getPreferredFormat( ) method to convert a BMP image format and return the preferred image format.

  - Calls the ProcessCopy( ) method in the `OrdImage` class to process the full-size image, create a thumbnail image, and populate an OrdImage object named `thumb`.

  - Executes a SQL UPDATE statement to update the full-size and thumbnail images in the database.

  - Displays a photo upload success message and then directs the browser to refresh the page.

- A getPreferredFormat( ) private function, in this sample application, converts a BMP image format and returns the preferred image file format based on the number of colors in the image; returns a MONOCHROME image format if there are no colors, or a JPEG if there are more than 8 colors, or a GIF if there are greater than 0 and fewer than 8 colors.

- A printPageHeader( ) private function displays an HTML header that is common to all HTML responses.

- A printPageTrailer( ) private function displays an HTML trailer that is common to all HTML responses.

- A printMessage( ) private function prints a message on the HTML page.

- A printHeading( ) private function prints a header on the HTML page.

- A printLink( ) function produces an HTML anchor tag in a standard format.

### 3.2.2 Oracle interMedia JavaServer Pages (JSP) Photo Album Sample Application

The interMedia JavaServer Pages (JSP) Photo Album sample application is a JSP application that demonstrates the use of Oracle interMedia Java Classes for Servlets and JSP to upload and retrieve multimedia data to and from a database. Users access the JSP files that constitute the application to view the contents of the photo album, including thumbnail versions of each photograph, to view the full-size version of any photograph, and to upload new photographs into the album.

#### 3.2.2.1 Running the JSP Photo Album Application

After you have completed the setup tasks and have built the JSP photo album application, you can run the JSP photo album application by entering the following URL in the address field of your Web browser:

- Default installation of Oracle Application Server or Oracle Database

      <protocol><hostname:port-number>/demo/PhotoAlbum.jsp

- Default installation of Tomcat 3.2 on Windows

      <protocol><hostname:port-number>/examples/jsp/PhotoAlbum.jsp

  The `<protocol>` field is `http://`, and the `<hostname:port-number>` field is the host name and port number of the system where your HTTP server is running.

When first invoked, the photo album application displays any images that are currently stored in the album. By default, the photo album is empty when first installed. To upload a new photograph, select Upload new photo. Enter a description of the photograph, the location where the photograph was taken, and the name of the image file or browse to its directory location, then click Upload photo. The contents of the photo album are displayed along with a picture of the new photograph. Click the thumbnail image to view the full-size version of the photograph.

When the photo album application displays the text view image instead of its thumbnail image, the image format that was uploaded was not recognized by interMedia. Click view image to display the full-size image.

You can now begin to load your photo album application with your favorite photographs.

#### 3.2.2.2 Description of the interMedia JSP Photo Album Application

The JSP photo album application separates the business logic from the presentation by having a JavaBean containing methods that are accessed from each of five JSP files. When compiled, the application creates the `PhotoAlbumBean.class` file, which contains the user-entered connection information and defines the functions: getId( ), getDescription( ), getLocation( ), and getPreferredFormat( ) and the following methods:

selectTable( )
selectRowById( )
fetch( )
insertNewPhoto( )
release( )
getConnection( )
freeConnection( )
setId( )
setDescription( )
setLocation( )
getImage( )
getThumb( )

To follow along with the description of tasks, users should refer to a copy of each JSP file, which can be found in:

`<ORACLE_HOME>``/ord/http/demo/jsp` (on UNIX)

`<ORACLE_HOME>``\ord\http\demo\jsp` (on Windows)

In the `PhotoAlbumEntryViewer`, `PhotoAlbumMediaViewer`, `PhotoAlbum`, and `PhotoAlbumInsertPhoto` JSP files, the `jsp:useBean` action tag is used to establish an ID and association with the `PhotoAlbumBean` class and the `OrdHttpJspResponseHandler` and `OrdHttpUploadFormData` classes of Oracle interMedia Java Classes for Servlets and JSP. For example, the following code appears in the `PhotoAlbumInsertPhoto` JSP file:

    <jsp:useBean id="album" scope="page" class="PhotoAlbumBean"/>
    <jsp:useBean id="handler" scope="page"
                 class="oracle.ord.im.OrdHttpJspResponseHandler"/>
    <jsp:useBean id="formData" scope="page"             class="oracle.ord.im.OrdHttpUploadFormData"/>

This `jsp:useBean` action tag is used so these objects can be referenced by their respective ID values (`album`, `handler`, and `formData`) to call the methods of these classes.

The `OrdHttpUploadFile` class of Oracle interMedia Java Classes for Servlets and JSP is defined as an object with the name `uploadPhoto` in the insertNewPhoto( ) method in the `PhotoAlbumBean.java` file and then used to call its loadImage( ) method to load the photograph into the `photos` table as shown in the following code excerpts:

    public void insertNewPhoto( OrdHttpUploadFile uploadPhoto )
            throws SQLException, ServletException, IOException
    .
    .
    .
    uploadPhoto.loadImage( image );
    .
    .
    .

The insertNewPhoto( ) method defined in the `PhotoAlbumBean.java` file, uses the ORAData (formerly getCustomDatum) and ORADataFactory (formerly getFactory) interfaces supplied by Oracle to upload an image and a thumbnail image in an OrdImage Java object. First, the method executes a SQL SELECT...FOR UPDATE statement to select the row for update, and then, executes a SQL UPDATE statement to update the `image` and `thumb` columns for that row in the `photos` table as shown in the following code excerpts:

    stmt = (OraclePreparedStatement)conn.prepareStatement(
                "select image,thumb from photos where id = ? for update" );
    stmt.setString( 1, id );
    rset = (OracleResultSet)stmt.executeQuery();
    if ( !rset.next() )
    {
        throw new ServletException( "new row not found in table" );
    }
    image = (OrdImage)rset.getORAData( 1, OrdImage.getORADataFactory());
    thumb = (OrdImage)rset.getORAData( 2, OrdImage.getORADataFactory());

    rset.close();
    stmt.close();
    .
    .
    .
        //
        // Prepare and execute a SQL statement to update the full-sized and
        // thumbnail images in the database.
        //
        stmt = (OraclePreparedStatement)conn.prepareStatement(
                   "update photos set image = ?, thumb = ? where id = ?" );
        stmt.setORAData( 1, image );
        stmt.setORAData( 2, thumb );
        stmt.setString( 3, id );
        stmt.execute();
        stmt.close();

        //
        // Commit the changes.
        //
        conn.commit();
    }

The fetch( ) method defined in the `PhotoAlbumBean.java` file or the PhotoAlbumBean JavaBean, fetches the next row from the result set using the ORAData and ORADataFactory interfaces to retrieve the image and the thumbnail image from an OrdImage Java object, and delivers each to the browser, as shown in the following example:

    public boolean fetch()
        throws SQLException
    {
        if ( rset.next() )
        {
            id = rset.getString( 1 );
            description = rset.getString( 2 );
            location = rset.getString( 3 );
            image = (OrdImage)rset.getORAData( 4, OrdImage.getORADataFactory() );
            thumb = (OrdImage)rset.getORAData( 5, OrdImage.getORADataFactory() );
            return true;
        }
        else
        {
            rset.close();
            stmt.close();
            return false;
        }
    }

What follows is a more detailed description of each JSP file.

PhotoAlbum.jsp

This JSP file is the entry point to the JSP photo album application and does the following:

- Uses the PhotoAlbumBean JavaBean to access the contents of the `photos` table.

- Uses the `OrdHttpJspResponseHandler` class to facilitate the retrieval of image data from the `photos` table and its delivery to a browser or other HTTP client from a Java servlet.

- Displays the title of the page in the HTML header and in the common page header.

- Displays the thumbnail images in a table using column headers labeled, `Description`, `Location`, and `Image`.

- Uses a `try/catch` block to ensure the JDBC connection is released.

- Calls the selectTable( ) method to select all the rows in the `photos` table.

- Initializes the row count to zero.

- Displays an entry in the photo album by calling the getDescription( ) method, then the getLocation( ) method, and then printing the values in the appropriate columns. If the location information is blank, print a space in the `Location` column.

- Displays the contents of the photo album as thumbnail images using an HTML anchor tag to call the `PhotoAlbumEntryViewer.jsp` file to get the ID value by calling the getID( ) function.

- Calls the getThumb( ) method to get the thumbnail image and calls the getContentLength( ) method to determine the image length.

- Tests to see if the value returned for the image length is greater than 0, and if so uses an image tag of the form `<IMG SRC="PhotoAlbumMediaViewer.jsp?media=thumb&...>` to display the thumbnail image; otherwise, prints the link view image in the column header labeled `Image`, which, when clicked, retrieves the full-size image.

- Displays a message "The photo album is empty" if the photo album is empty. If the photo album is not empty, the following message is displayed "Select the thumbnail to view the full-sized image".

- Ends the `try/catch` block with a `finally` clause and releases the JDBC connection by calling the release( ) method.

- Displays a link to the upload form with the text Upload new photo at the bottom of the page that calls the `PhotoAlbumUploadForm.jsp` file.

PhotoAlbumEntryViewer.jsp

This JSP file is called by the `PhotoAlbum.jsp` file that displays one full-size version of a photograph in the album. This JSP file does the following:

- Uses the PhotoAlbumBean JavaBean to access the contents of the `photos` table.

- Uses the `OrdHttpJspResponseHandler` class to facilitate the retrieval of image data from the `photos` table and its delivery to a browser or other HTTP client from a Java servlet.

- Displays the title of the page in the HTML header and in the common page header.

- Defines a string named `id` that calls the getParameter( ) method to get the `id` value.

- Displays a message "Malformed URL, no id parameter" in the event the value of the `id` string is null.

- Uses a `try/catch` block to ensure the JDBC connection is released.

- Calls the selectRowById( ) method with the value of `id` to select the entry to be displayed. If the next row to be fetched for that `id` value is not found, display a message "Entry not found: <id value>".

- Displays the entry in the album by calling the getDescription( ) method and displaying its value under the header `Description`, calling the getLocation( ) method and displaying its value under the `Location` header.

- Displays one full-size version of a photograph in the album using an image tag in the form `<IMG SRC="PhotoAlbumMediaViewer.jsp?media=image&...">` under the `Photo` header.

- Displays the full-size images height and width by calling the getHeight( ) and getWidth( ) methods. If the image format is not recognized by interMedia, height and width values will be zero and will not be displayed.

- Displays a link at the bottom of the page Return to photo album that calls the `PhotoAlbum.jsp` file.

- Ends the `try/catch` block, and with a `finally` clause, releases the JDBC connection by calling the release( ) method.

PhotoAlbumMediaViewer.jsp

This JSP file is called by the `PhotoAlbum.jsp` and `PhotoAlbumEntryViewer.jsp` files and retrieves a single thumbnail or full-size image from the `photos` table using the PhotoAlbumBean JavaBean and delivers it to the browser using the `OrdHttpResponseHandler` class. This JSP file does the following:

- Uses the PhotoAlbumBean JavaBean to access the contents of the `photos` table.

- Uses the `OrdHttpJspResponseHandler` class to facilitate the retrieval of image data from the `photos` table and its delivery to a browser or other HTTP client from a Java servlet.

- Defines a string named `id` that calls the getParameter( ) method to get the `id` value.

- Defines a string named `media` that calls the getParameter( ) method to get the `media` value.

- Sets a condition to proceed as long as the value of the string `id` and the value of the string `media` is not null.

- Uses a `try/catch` block to ensure the JDBC connection is released.

- Calls the selectRowById( ) method to select a specific row from the `photos` table for the value of `id`.

- Delivers the full-size or thumbnail image by first calling the setPageContext( ) method of the `OrdHttpJspResponseHandler` class to specify the page context object; then, calling the getImage( ) method to return the image to the OrdImage object; then, calling the sendImage( ) method of the `OrdHttpResponseHandler` class to retrieve the image from the OrdImage object and deliver it to the browser. If the value of media is `image`, an image is delivered to the browser; if the value of media is `thumb`, a thumbnail image is delivered to the browser. The sendImage( ) method supports browser content caching by supporting the If-Modified-Since and Last-Modified headers.

- Ends the `try/catch` block with a `finally` clause and releases the JDBC connection by calling the release( ) method.

- Displays the following message in the event the request is not understood "PhotoAlbumMediaViewer.jsp - malformed URL".

PhotoAlbumUploadForm.jsp

This JSP file is called by the `PhotoAlbum.jsp` file that displays an HTML form to allow users to upload new photographs into the album. This JSP file does the following:

- Displays the title of the page in the HTML header and in its common page header.

- Displays any error message under the header "Error message" from a previous attempt to upload an image to determine whether or not the value of a string is not null after calling the getParameter( ) method with an argument of `error`.

- Displays a header with the text Upload a new photo.

- Defines the form action specifying the `PhotoAlbumInsertPhoto.jsp` file to process the upload request as a multipart/form-data POST request.

- Displays the upload form with rows labeled `Description`, `Location`, and `File name:`.

- Displays the contents of the upload form defined as a table with rows labeled `Description` and `Location`, both with an input type of text and named description and location respectively, followed by a row labeled `File name:` with an input type of `file` and named `photo`, and finally followed by a row with no label and an input type of `submit` and a value of `Upload photo`.

- Displays a link at the bottom of the page Return to photo album that calls the `PhotoAlbum.jsp` file.

PhotoAlbumInsertPhoto.jsp

This JSP file is called by the `PhotoAlbumUploadForm.jsp` file that uses the `OrdHttpUploadFormData` class to parse the POST data in a POST request containing the uploaded photograph. This JSP file does the following:

- Uses the PhotoAlbumBean JavaBean to access the contents of the `photos` table.

- Uses the `OrdHttpJspResponseHandler` class to facilitate the retrieval of image data from the `photos` table and its delivery to a browser or other HTTP client from a JSP file.

- Uses the `OrdHttpUploadFormData` class to facilitate the processing of POST requests by parsing the POST data containing the multipart/form-data encoding, and making the contents of regular form fields and uploaded files readily accessible to a JSP file.

- Sets the value of the strings `description` and `location` to `null` and the OrdHttpUploadFile object uploadPhoto to `null`.

- Uses a `try/catch` block to ensure the JDBC connection is released.

- Passes an OrdHttpUploadFile object to the `PhotoAlbumBean` class to store the photograph in the database.

- Calls the setServletRequest( ) method of the `OrdHttpUploadFormData` class to specify the ServletRequest object for the request.

- Tests to see if the request is encoded using the multipart/form-data encoding by calling the isUploadRequest( ) method of the `OrdHttpUploadFormData` class.

- Forwards the request to the `PhotoAlbumUploadForm.jsp` file if the call to the isUploadRequest( ) method returns a Boolean expression of not false.

- Parses the form data by calling the parseFormData( ) method of the `OrdHttpUploadFormData` class.

- Gets the form field values for description and location by calling the getParameter( ) method of the `OrdHttpUploadFormData` class, and also gets the name of the file to be uploaded by calling the getFileParameter( ) method of the same class.

- Tests to make sure the file name is not null from the getFileParameter( ) method call of the `OrdHttpUploadFormData` class, then calls the getOriginalFileName( ) method of the `OrdHttpUploadFile` class to ensure that the original file name as provided by the browser is not null, or that the content length of the file is empty by calling the getContentLength( ) method of the `OrdHttpUploadFile` class.

- Forwards the request to the `PhotoAlbumUploadForm.jsp` file if there is a valid image file.

- If the description is null or empty, uses the file name as the description by calling the getSimpleFileName( ) method of the `OrdHttpUploadFile` class.

- Inserts the new entry into the `photos` table by calling the setDescription( ), setLocation( ), and insertNewPhoto( ) methods in the `PhotoAlbumBean.java` JavaBean.

- Ends the `try/catch` block with a `finally` clause and releases the JDBC connection by calling the release( ) method and releases all resources held by the OrdHttpUploadFormData object by calling its release( ) method.

- Displays the updated photo album by displaying the title of the page in the HTML header and in its common page header, directing the browser to the main page by calling the `PhotoAlbum.jsp` file, then displays the header "Photo successfully uploaded into photo album" and the instruction, "Please click on link below or wait for the browser to refresh the page".

- Displays a link at the bottom of the main page Return to photo album that calls the `PhotoAlbum.jsp` file.

PhotoAlbumBean.java

This is a JavaBean used by the JSP files to access the database.

The first call to the JavaBean for a request causes it to allocate a JDBC connection from a connection pool. Subsequent calls by the same request reuse the same connection. At the end of a request, each JSP file is responsible for calling the JavaBean to release the JDBC connection back to the pool. Each HTTP GET or POST request is assigned its own JDBC connection from the pool to ensure that multiple requests can be serviced concurrently.

The following methods are defined:

- The selectTable( ) method selects all the rows in the `photos` table, orders them by location, and returns the results in a result set.

- The selectRowById( ) method selects a specific row from the `photos` table where the value of `id` in the `where` clause is a parameter marker and returns the results in a result set.

- The fetch( ) method fetches the next row from the result set.

- The insertNewPhoto( ) method does the following:

  - Uses the `OrdHttpUploadFile` class to upload the new photograph into the database.

  - Disables auto-commit by calling the setAutoCommit( ) method with an argument of false.

  - Executes a SQL SELECT photos_sequence.nextval statement to get the next value for the value of the `id` column for the new row to be inserted into the `photos` table.

  - Executes a SQL INSERT statement to insert a new row in the `photos` table.

  - Executes a SQL SELECT...FOR UPDATE statement to fetch the initialized full-size and thumbnail image objects from the `photos` table.

  - Loads the image by calling the loadImage( ) method in the `OrdHttpUploadFile` class to populate an OrdImage object named `image` with the full-size image, and sets the properties or attribute values of the image object based on the image contents.

  - Gets the image file format by calling the getContentFormat( ) method and if it is not null, and if the MIME type is BMP, then tries to process the image by calling the process( ) method and calling the getPreferredFormat( ) method to convert it to a MONOCHROME, GIF, or JPEG image format, based on the number of colors in the image.

  - Tries to copy the full-size image and process it to create the thumbnail image by calling the processCopy( ) method in the `OrdImage` class and populate the OrdImage object named `thumb`.

  - Executes a SQL UPDATE statement to update the full-size and thumbnail images in the database.

  - Commits the changes.

- A release( ) method to release the result set and statement objects, and places the JDBC connection back on the free list or stack.

- Get methods (getId( ), getDescription( ), getLocation( ), getImage( ), and getThumb( )) and the set methods (setId( ), setDescription( ), and setLocation( )) are used to get or set attributes for all attributes or columns.

- A getConnection( ) private function implements a simple JDBC connection pool.

- A freeConnection( ) private function releases the JDBC connection back to the pool at the end of the request.

- A getPreferredFormat( ) private function returns the preferred image file format based on the number of bits of color in the BMP image; returns a MONOCHROME image if there are no bits of color, returns JPEG if there are more than 8 bits of color, or returns GIF if there are between 1 and 8 bits of color.

### 3.2.3 Oracle interMedia ASP/VBScript Photo Album Sample Application

The interMedia ASP/VBScript Photo Album sample application is an ASP/VBScript application that demonstrates how to upload, retrieve, and process multimedia data stored using the interMedia ORDImage type and Oracle Objects for OLE. Users access the application to view the contents of the photo album, including thumbnail versions of each photograph, to view the full-size version of any photograph, and to upload new photographs into the album.

#### 3.2.3.1 Running the ASP/VBScript Photo Album Application

After you have installed and configured this photo album application in Microsoft IIS and configured the application connection parameters, you are ready to run the photo album application.

To use it, enter the photo album URL into the location bar of your Web browser, for example:

    http://<hostname:port>/photoAlbum

When first invoked, the application displays any images that are currently stored in the album. By default, the photo album is empty when first installed. To upload a new photograph, click Upload new photo. Enter a description of the photograph, the location where the photograph was taken, and the name of the image file or browse to its directory location, then click Upload new photo. The contents of the photo album are then displayed along with a picture of the new photograph. Click the thumbnail image to view the full-size version of the photograph.

When the photo album application displays the text view image instead of its thumbnail image, the image format that was uploaded was not recognized by interMedia. Click view image to display the full-size image.

You can now begin to load your photo album application with your favorite photographs.

#### 3.2.3.2 Description of the ASP/VBScript Photo Album Application

The top-level files that implement the photo album application are: `viewAlbum.asp`, `viewEntry.asp`, and `uploadPhoto.asp`. In addition, the `getPhoto.asp` file retrieves the images from the database and the `insertPhoto.asp` file inserts a new image into the database.

viewAlbum.asp

The viewAlbum page displays the contents of the photo album in a tabular format with columns labeled `Description`, `Location`, and `Image`.

Thumbnail images are ordered by description in the SQL SELECT statement and displayed with an anchor tag that is used to display the full-size image, using the tag `<img src="getPhoto.asp?entry_id=...">` as follows:

    <A href="viewEntry.asp?entry_id=<%=strId%>">
           <% If objThumb.contentlength > 0 Then %>
             <IMG border = 1
                  height="<%=objThumb.height%>"
                  width="<%=objThumb.width%>"
                  alt="<%=strDescription%>"
                  src="getPhoto.asp?media=thumb&entry_id=<%=strId%>"
             >

If interMedia does not support the image format, then a thumbnail image would not have been created and stored in the database. In this case, the text view image is displayed instead of the thumbnail image in the `Image` column header of the table.

Text is displayed on the page stating Select the thumbnail to view the full-size image. A link appearing at the bottom of the page Upload new photo, calls the `uploadPhoto.asp` file to present an upload form (`uploadForm.inc`) to assist in uploading a new photograph into the database.

viewEntry.asp

The viewEntry page, which displays the full-size version of a photograph, also uses the tag `<img src="getPhoto.asp?entry_id...">` to display an image, as follows:

    <TD vAlign=top scope="col"><B>Photo:</B></TD>
        <TD scope="col">
           <IMG border=1
                alt="<%=strDescription%>"
                src="getPhoto.asp?media=image&entry_id=<%=strId%>"
           <% If objImage.height > 0 And objImage.width > 0 Then %>
                height="<%=objImage.height%>"
                width="<%=objImage.width%>"
           <% End If %>
           >
        </TD>

Both URLs will retrieve an image from the database and deliver it to the browser using Oracle Objects for OLE to communicate with the database.

A link appears at the bottom of the page Return to photo album that calls the `viewAlbum.asp` file to present the photo album table and its set of thumbnail images to view.

uploadPhoto.asp

The uploadPhoto page displays an HTML form (`uploadForm.inc`) that allows a user to upload a new photograph into the database by entering description and location information for the new photograph, and its image file name. The form invokes the insertPhoto page as follows:

    <FORM action="insertPhoto.asp" method="post" encType="multipart/form-data">

insertPhoto.asp

The insertPhoto page performs the work of loading the image into the `photos` table and automatically generating a thumbnail version of the image.

Clicking Upload photo near the bottom of the uploadPhoto page executes the submit input type form action, as follows:

    <TD colSpan=2><INPUT type=submit value="Upload photo"></TD>

getPhoto.asp

The getPhoto page retrieves the image, either a thumbnail or full-size image, based on its photo column indicator value (`thumb` or `image`), from the database and returns it to the browser. If the image requested is in the browser cache and the cache is valid, then it retrieves the image from cache; otherwise, it sets the MIME type of the image based on its attribute value in the database, then gets the image from the database and delivers it to the browser, as follows:

    If CacheIsValid( setPhotos(1).value ) Then
      Response.Status = HTTP_STATUS_NOT_MODIFIED
    Else
      ' Set the mime type header and deliver the image to the browser.
      SetLastModified( setPhotos(1).value )
      Response.ContentType = objMedia.mimetype
      ReadBlob objMedia.source.localData
    End If

## 3.3 Oracle interMedia Code Wizard Sample Application

The interMedia Code Wizard sample application lets you create PL/SQL stored procedures for the PL/SQL Gateway to upload and retrieve media data (images, audio, video, and general media) stored in a database using interMedia object types, ORDImage, ORDAudio, ORDVideo, and ORDDoc, and their respective methods. The Code Wizard guides you through a series of self-explanatory steps to create either a media retrieval or a media upload procedure. You can either create and compile standalone media access procedures, or you can create the source of media access procedures for inclusion in a PL/SQL package. This is similar to how the photo album application (see Section 3.1) uses the `insert_new_photo` procedure as the image upload procedure and the `deliver_media` procedure as the image retrieval procedure in the `photo_album` PL/SQL package. Finally, once created, you can customize the media access procedures as necessary to meet specific application requirements.

### 3.3.1 Using the Code Wizard

The interMedia Code Wizard sample application can be installed from the Companion CD of Oracle Database. The application is installed in the following directory:

`<ORACLE_HOME>``/ord/http/demo/plsgwycw`

The `README.txt` file in this directory contains complete instructions on installing and configuring the Code Wizard sample application. The following discussion assumes that the Code Wizard has been installed into the ORDSYS schema.

To use the Code Wizard to create and test media upload and retrieval procedures, you must do the following steps:

1. Create a new DAD or choose an existing DAD for use with the Code Wizard.

2. Authorize use of the DAD using the Code Wizard's administration function.

3. Create and test media upload and retrieval procedures.

This section describes each of these topics in more detail as the following topics:

- Creating a New DAD or Choosing an Existing DAD

- Authorizing a DAD

- Creating and Testing Media Upload and Retrieval Procedures

- Using the PL/SQL Gateway Document Table

- How Time Zone Information Is Used to Support Browser Caching

#### 3.3.1.1 Creating a New DAD or Choosing an Existing DAD

To create media upload or retrieval procedures, you must select one or more DADs for use with the Code Wizard. To prevent the unauthorized browsing of schema tables and to prevent the unauthorized creation of media access procedures, you must authorize each DAD using the Code Wizard's administration function. Depending on your database and application security requirements, you may choose to create and authorize one or more new DADs specifically for use with the Code Wizard, or you may choose to authorize the use of one or more existing DADs.

Oracle recommends that any DAD authorized for use with the Code Wizard should use some form of user authentication mechanism. The simplest approach is to create or use a DAD that uses database authentication. To use this approach, select Basic Authentication Mode and omit the Password in the DAD specification. Alternatively, you may choose to use a DAD that specifies an existing application-specific authentication mechanism. For more information about configuring DADs, see Oracle HTTP Server Administrator's Guide in the Oracle Application Server 10g Online Documentation Library.The following example describes how to create a DAD to create and test media upload and retrieval procedures in the `SCOTT` schema.

Note: To test media upload procedures, the name of a document table must be specified in the DAD. When testing an upload procedure, you may choose the DAD you use to create the procedure, or you may use the DAD used to access the application. You may choose a document table name when you create a DAD, edit a DAD to specify the document table name at a later time, or use an existing DAD that already specifies a document table name. This example illustrates specifying the document table name when you create the DAD.

1. Set your Web browser to the Oracle HTTP Server Home page. Select "PL/SQL Properties" in the "Administration" page. This opens the `mod_plsql` Services page.

2. On the `mod_plsql` Services page, scroll to the DAD Status section. Click Create. This opens the DAD Type page.

3. Select the DAD type to be "General". Click Next. This opens the Database Connection page.

4. Enter "/scottw" in the DAD Name field. Enter SCOTT for the database account, and leave the password blank. Enter the connection information in the Database Connectivity Information section. In the Default page field, enter ORDCWPKG.MENU. Leave the other fields blank. Click Next. This opens the Document, Alias, and Session page.

5. On the Document, Alias, and Session page, enter MEDIA_UPLOAD_TABLE for the Document Table. Click Apply.

6. Restart Oracle HTTP Server for the changes to take effect.

#### 3.3.1.2 Authorizing a DAD

To authorize a DAD for use with the Code Wizard, do the following steps:

1. Enter the Code Wizard's administration URL into your browser's location bar, for example:

       http://<host-name>:<port-number>/ordcwadmin

2. Enter the ORDSYS user name and password when prompted by the browser.

3. Select DAD authorization from the Main menu as shown in Figure 3-8, then click Next.

   Figure 3-8 Main Menu for the interMedia Code Wizard for the PL/SQL Gateway

   Description of the illustration imwizpls.gif

4. Enter the name of the DAD you wish to authorize together with the user name, as shown in Figure 3-9, then click Apply.

   Figure 3-9 Authorizing the SCOTTCW DAD

   Description of the illustration authdad.gif

   Note: Duplicate DADs are not allowed, and each authorized DAD must indicate which database schema the user is authorized to access with the Code Wizard, using the DAD. Use this same page to delete the authorization for any existing DADs that no longer need to use the Code Wizard.

5. Review the updated list of DADs that are authorized to use the interMedia Code Wizard, as shown in Figure 3-10, then click Next.

   Figure 3-10 List of Authorized DADs

   Description of the illustration listdads.gif

6. To log out (clear HTTP authentication information), select Logout from the Main menu, then click Next. The log out operation redirects the request to the PL/SQL Gateway's built-in `logmeoff` function. For more information, see Oracle Application Server mod_plsql User's Guide.

#### 3.3.1.3 Creating and Testing Media Upload and Retrieval Procedures

To start the Code Wizard, enter the appropriate URL into your browser's location bar, for example:

`http://``<hostname>``:``<port-number>``/scottcw`

or

`http://``<hostname>``:``<port-number>``/mediadad/ordcwpkg.menu`

Then, enter the user name and password when prompted by the browser. The Main menu page of the interMedia Code Wizard for the PL/SQL Gateway is displayed as shown in Figure 3-11.

Figure 3-11 Using the SCOTTCW DAD

Description of the illustration scottcw.gif

If the DAD is configured specifically for use with the Code Wizard, simply enter the DAD name. Alternatively, to use another DAD, enter the DAD name together with the Code Wizard package name and Main menu procedure name, `ORDCWPKG.MENU` after the DAD name.

Once you have logged in, you can log out (clear HTTP authentication information) at any time by selecting Logout from the Main menu, then clicking Next. The logout operation redirects the request to the PL/SQL Gateway's built-in `logmeoff` function. For more information, see Oracle Application Server mod_plsql User's Guide.

To create a media upload procedure (see Section 3.3.1.4) or a media retrieval procedure (see Section 3.3.1.5), select the appropriate option from the Main menu, then click Next. The Code Wizard then guides you through a series of self-explanatory steps to create the procedure.

If you create a standalone media upload or retrieval procedure, you will have the opportunity to view the contents of the procedure as well as to test it.The image and multimedia sample sessions described in Section 3.3.2 and Section 3.3.3 respectively, illustrate how to create and test a media upload procedure and a media retrieval procedure.

#### 3.3.1.4 Creating a Media Upload Procedure

To create a media upload procedure using the interMedia Code Wizard for the PL/SQL Gateway, do the following steps:

1. At the Main menu, select Create media upload procedure as shown in Figure 3-12. Click Next.

   Figure 3-12 Create a Media Upload Procedure

   Description of the illustration medupld.gif

2. At Step 1: Select database table and procedure type, select CW_IMAGES_TABLE and Standalone procedure as shown in Figure 3-13. Click Next.

   Figure 3-13 Step 1: Select Database Table and Procedure Type

   Description of the illustration imgupld1.gif

3. At Step 2: Select PL/SQL Gateway document upload table, select Use existing document table and select MEDIA_UPLOAD_TABLE because the `SCOTTCW` DAD is configured to use this document table as shown in Figure 3-14 and Figure 3-15. Click Next.

   Figure 3-14 Step 2: Select PL/SQL Gateway Document Upload Table (Part 1)

   Description of the illustration imgupl2a.gif

   Figure 3-15 Step 2: Select PL/SQL Gateway Document Upload Table (Part 2)

   Description of the illustration imgupl2b.gif

4. At Step 3: Select data access and media column(s), ensure IMAGE (ORDIMAGE) is checkmarked, that ID (Primary key) is selected, and select Conditional insert or update as shown in Figure 3-16. Click Next.

   Figure 3-16 Step 3: Select Data Access and Media Column(s)

   Description of the illustration imgupld3.gif

5. At Step 4: Select additional columns and procedure name, ensure DESCRIPTION is checkmarked, accept the default procedure name, UPLOAD_CW_IMAGES_TABLE_IMAGE, and select Create procedure in the database as shown in Figure 3-17. Click Next.

   Figure 3-17 Step 4: Select Additional Columns and Procedure Name

   Description of the illustration imgupld4.gif

6. At Step 5: Review selected options, review the options you have selected as shown in Figure 3-18. If the options selected are correct, click Finish.

   Figure 3-18 Step 5: Review Selected Options

   Description of the illustration imgupld5.gif

7. At the Compile procedure and review generated source window note the message, `Procedure created successfully: UPLOAD_CW_IMAGES_TABLE_IMAGE` as shown in Figure 3-19. To review the compiled PL/SQL source code in another window, click View (see Step 5, substep 6g in Section 3.3.2 for a copy of the generated upload procedure). Assuming you have configured the `SCOTTCW` DAD and specified `MEDIA_UPLOAD_TABLE` as the document table, in the DAD: field, the DAD name `scottcw` is displayed by default. To test the PL/SQL procedure created, click Test.

   Figure 3-19 Compile Procedure and Review Generated Source

   Description of the illustration imgupld6.gif

8. At the inter Media Code Wizard: Template Upload Form window, enter the value `1` in the ID field, browse for and select the image you want to upload in the IMAGE field, and enter a brief description of the image to be uploaded in the DESCRIPTION field as shown in Figure 3-20. Click Upload media.

   Figure 3-20 Template Upload Form

   Description of the illustration imgupld7.gif

9. The image is uploaded into the table row and a message is displayed, as shown in Figure 3-21.

   Figure 3-21 Template Upload Procedure -- Media Uploaded Successfully Message

   Description of the illustration imgupld8.gif

10. Return to the Compile procedure and review generated source window. If you are finished testing, click Done to return to the Main menu.

#### 3.3.1.5 Creating a Media Retrieval Procedure

To create a media retrieval procedure using the interMedia Code Wizard for the PL/SQL Gateway, do the following steps:

1. At the Main menu, select Create media retrieval procedure as shown in Figure 3-22. Click Next.

   Figure 3-22 Create a Media Retrieval Procedure

   Description of the illustration medrt.gif

2. At Step 1: Select database table and procedure type, select CW_IMAGES_TABLE and select Standalone procedure as shown in Figure 3-23. Click Next.

   Figure 3-23 Step 1: Select Database Table and Procedure Type

   Description of the illustration imgrt1.gif

3. At Step 2: Select media column and key column, ensure IMAGE (ORDIMAGE) and ID (Primary key) are selected as shown in Figure 3-24. Click Next.

   Figure 3-24 Step 2: Select Media Column and Key Column

   Description of the illustration imgrt2.gif

4. At Step 3: Select procedure name and parameter name, accept the default procedure name, `GET_CW_IMAGES_TABLE_IMAGES`, accept the default parameter name, `MEDIA_ID`, and accept Create procedure in the database as shown in Figure 3-25. Click Next.

   Figure 3-25 Step 3: Select Procedure Name and Parameter Name

   Description of the illustration imgrt3.gif

5. At Step 4: Review Selected Options, review the options you have selected as shown in Figure 3-26. If the options selected are correct, click Finish.

   Figure 3-26 Step 4: Review Selected Options

   Description of the illustration imgrt4.gif

6. At the Compile procedure and review generated source window note the message, `Procedure created successfully: GET_CW_IMAGES_TABLE_IMAGE` as shown in Figure 3-27. To review the compiled PL/SQL source code in another window, click View (see Step 6, substep 5e in Section 3.3.2 for a copy of the generated retrieval procedure). To test the PL/SQL procedure created, assuming you have an image already loaded in the database with an ID value of `1`, enter the value `1` for the Key parameter (`MEDIA_ID`), then click Test. The image is retrieved from the table row and is displayed as shown in Figure 3-28. Click Done to return to the Main menu.

   Figure 3-27 Compile Procedure and Review Generated Source

   Description of the illustration imgrt5.gif

   Figure 3-28 Displayed Image 1981

   Description of the illustration imgupld9.gif

#### 3.3.1.6 Using the PL/SQL Gateway Document Table

All files uploaded using the PL/SQL Gateway are stored in a document table. Media upload procedures created by the Code Wizard automatically move uploaded media from the specified document table to the application's table. To avoid transient files from appearing temporarily in a document table used by another application component, use a document table that is not being used to store documents permanently.

Be sure to specify the selected document table in the application's database access descriptor (DAD). If the DAD already specifies a different document table, create a new DAD for media upload procedures. If you choose to create a new document table, the Code Wizard will create a table with the following format:

    CREATE TABLE document-table-name
      ( name           VARCHAR2(256) UNIQUE NOT NULL,
        mime_type      VARCHAR2(128),
        doc_size       NUMBER,
        dad_charset    VARCHAR2(128),
        last_updated   DATE,
        content_type   VARCHAR2(128),
        blob_content   BLOB );

For more information about file upload and document tables, see Oracle Application Server mod_plsql User's Guide.

#### 3.3.1.7 How Time Zone Information Is Used to Support Browser Caching

User response times are improved and network traffic is reduced if a browser can cache resources received from a Web server and subsequently use those cached resources to satisfy future requests. This section describes at a very high level, how the browser caching mechanism works and how the Code Wizard utility package is used to support that mechanism. When reading this discussion, note that all HTTP date and time stamps are expressed in Coordinated Universal Time (UTC).

All HTTP responses include a Date header, which indicates the date and time when the response was generated. When a Web server sends a resource in response to a request from a browser, it can also include the Last-Modified HTTP response header, which indicates the date and time when the requested resource was last modified. It is important to note that the Last-Modified header must not be later than the Date header.

After receiving and caching a resource, if a browser needs to retrieve the same resource again, it sends a request to the Web server with the If-Modified-Since request header specified as the value of the Last-Modified date, which was returned by the application server when the resource was previously retrieved and cached. When the Web server receives the request, it compares the date in the If-Modified-Since request header with the last update time of the resource. Assuming the resource still exists, if the resource has not changed since it was cached by the browser, the Web server responds with an HTTP `304 Not Modified` status with no response body, which indicates that the browser can use the resource currently stored in its cache. Assuming once again the resource still exists, if the request does not include an If-Modified-Since header or if the resource has been updated since it was cached by the browser, the Web server responds with an HTTP `200 OK` status and sends the resource to the browser. See the HTTP specification (`http://www.w3.org/Protocols/`) for more information.

The ORDImage, ORDAudio, ORDVideo, and ORDDoc objects all possess an updateTime attribute stored as a DATE in the embedded ORDSource object. Although the DATE data type has no support for time zones or daylight savings time, the Oracle9i and later database versions do support time zones and also provide functions for converting a DATE value stored in a database to UTC. See Oracle Database Administrator's Guide for more information about how to set a time zone for a database. See Oracle Database SQL Reference for more information about date and time functions.

When a response is first returned to a browser, a media retrieval procedure sets the Last-Modified HTTP response header based on the updateTime attribute. If a request for media data includes an If-Modified-Since header, the media retrieval procedure compares the value with the updateTime attribute and returns an appropriate response. If the resource in the browser's cache is still valid, an HTTP `304 Not Modified` status is returned with no response body. If the resource has been updated since it was cached by the browser, then an HTTP `200 OK` status is returned with the media resource as the response body.Media retrieval procedures created by the Code Wizard call the utility package to convert a DATE value stored in the database to UTC. The utility package uses the time zone information stored with an Oracle9i or later database and the date and time functions to convert database date and time stamps to UTC. To ensure the resulting date conforms to the rule for the Last-Modified date described previously, the time zone information must be specified correctly. See Oracle Database Administrator's Guide for more information about how to set a time zone for a database.

### 3.3.2 Sample Session Using Images

The following sample session uses the `SCOTT` schema to illustrate the creation of image media upload and retrieval procedures. Substitute a different schema name if you want to use a different schema.This sample session assumes the interMedia Code Wizard has been installed in the ORDSYS schema.

Perform the following steps:

Step 1 Create a table to store images for the application by starting SQL*Plus and connecting to the `SCOTT` schema in the database.

For example:

    sqlplus SCOTT/TIGER[@<connect_identifer>]

    SQL> CREATE TABLE cw_images_table(id NUMBER PRIMARY KEY,
                                      description VARCHAR2(30) NOT NULL,
                                      location VARCHAR2(30),
                                      image ORDSYS.ORDIMAGE );

Step 2 Create the `SCOTTCW` DAD to be used to create the procedures.

1. Set your Web browser to the Oracle HTTP Server Home page. Select "PL/SQL Properties" in the "Administration" page. This opens the `mod_plsql` Services page.

2. On the `mod_plsql` Services page, scroll to the DAD Status section. Click Create. This opens the DAD Type page.

3. Select the DAD type to be "General". Click Next. This opens the Database Connection page.

4. Enter "/scottw" in the DAD Name field. Enter SCOTT for the database account, and leave the password blank. Enter the connection information in the Database Connectivity Information section. In the Default page field, enter ORDCWPKG.MENU. Leave the other fields blank. Click Next. This opens the Document, Alias, and Session page.

5. On the Document, Alias, and Session page, enter MEDIA_UPLOAD_TABLE for the Document Table. Click Apply.

6. Restart Oracle HTTP Server for the changes to take effect.

Step 3 Authorize the use of the `SCOTTCW` DAD and `SCOTT` schema with the Code Wizard.

1. Enter the Code Wizard's administration URL into your browser's location bar, then enter the `ORDSYS` user name and password when prompted by the browser, for example:

       http://<hostname>:<port-number>/ordcwadmin

2. Select the DAD authorization function from the Code Wizard's Main menu and click Next. Enter the name of the demonstration DAD, `SCOTTCW`, and the user name `SCOTT`, then click Apply. Click Done when the confirmation window is displayed.

Step 4 Change DADs to the `SCOTTCW` DAD.

1. Click Change DAD from the Code Wizard's Main menu.

2. Click Change to SCOTTCW, if it is not already selected, then click Next.

3. Enter the user name `SCOTT` and password `TIGER` when prompted for user name and password, then click OK.

   The Main menu now displays the current DAD as `SCOTTCW` and the current schema as `SCOTT`.

Step 5 Create and test the media upload procedure.

Click Create media upload procedure from the Main menu, then click Next.

1. Select the database table and procedure type.

   1. Click the CW_IMAGES_TABLE database table.

   2. Click Standalone procedure.

   3. Click Next.

2. Select the PL/SQL document upload table.

   If there are no document tables in the `SCOTT` schema, the Code Wizard displays a message indicating this situation. In this case, accept the default table name provided, `CW_SAMPLE_UPLOAD_TABLE`, then click Next.

   If there are existing document tables, but the `CW_SAMPLE_UPLOAD_TABLE` is not among them, click Create new document table, accept the default table name provided, `CW_SAMPLE_UPLOAD_TABLE`, then click Next.

   If the `CW_SAMPLE_UPLOAD_TABLE` document table already exists, ensure that the Use existing document table and the CW_SAMPLE_UPLOAD_TABLE options are selected. Click Next.

3. Select the data access and media columns.

   1. Click IMAGE (ORDIMAGE).

   2. Click ID (Primary key).

   3. Click Conditional insert or update.

   4. Click Next.

4. Select additional columns and procedure names.

   1. Ensure that DESCRIPTION checkmarked because this column has a `NOT NULL` constraint. (The LOCATION column is not checkmarked by default as there are no constraints on this column.)

   2. Accept the procedure name provided, `UPLOAD_CW_IMAGES_TABLE_IMAGE`.

   3. Click Create procedure in the database.

   4. Click Next.

5. Review the following selected procedure creation options that are displayed:

       Procedure type:        Standalone
       Table name:            CW_IMAGES_TABLE
       Media column(s):       IMAGE (ORDIMAGE)
       Key column:            ID
       Additional column(s):  DESCRIPTION
       Table access mode:     Conditional update or insert
       Procedure name:        UPLOAD_CW_IMAGES_TABLE_IMAGE
       Function:              Create procedure in the database

   Click Finish.

6. Compile the procedure and review the generated source information.

   The Code Wizard displays the following message: "`Procedure created successfully: UPLOAD_CW_IMAGES_TABLE_IMAGE`".

   1. At the option Click to display generated source:, click View to view the generated source in another window. A copy of the generated source is shown at the end of Step 5, substep 6g.

   2. Close the window after looking at the generated source.

   3. Accept the DAD: name provided, `SCOTTCW`, then click Test to produce another window that displays a template file upload form that you can use to test the generated procedure.

   4. To customize the template file upload form, select Save As... from your browser's File pull-down menu to save the HTML source for editing.

   5. To test the template upload form, enter the following information:

      - For the ID: column, enter the number `1` as the row's primary key.

      - For the IMAGE column, click Browse... and choose an image file to upload to the database.

      - For the DESCRIPTION column, enter a brief description of the image.

      - Click Upload media.

      The Code Wizard displays a template completion window with the heading inter Media Code Wizard: Template Upload Procedure, and, if the procedure is successful, the message: `Media uploaded successfully`.

   6. Close the window.

   7. Click Done on the Compile procedure and review generated source window to return to the Main menu of the Code Wizard.

   A copy of the generated image upload procedure is as follows:

       CREATE OR REPLACE PROCEDURE UPLOAD_CW_IMAGES_TABLE_IMAGE
         ( in_ID IN VARCHAR2,
           in_IMAGE IN VARCHAR2 DEFAULT NULL,
           in_DESCRIPTION IN VARCHAR2 DEFAULT NULL )
       AS
         local_IMAGE ORDSYS.ORDIMAGE := ORDSYS.ORDIMAGE.init();
         local_ID CW_IMAGES_TABLE.ID%TYPE := NULL;
         upload_size     INTEGER;
         upload_mimetype VARCHAR2( 128 );
         upload_blob     BLOB;
       BEGIN
         --
         -- Update the existing row.
         --
         UPDATE CW_IMAGES_TABLE mtbl
           SET mtbl.IMAGE = local_IMAGE,
               mtbl.DESCRIPTION = in_DESCRIPTION
           WHERE mtbl.ID = in_ID
           RETURN mtbl.ID INTO local_ID;
         --
         -- Conditionally insert a new row if no existing row is updated.
         --
         IF local_ID IS NULL
         THEN
           --
           -- Insert the new row into the table.
           --
           INSERT INTO CW_IMAGES_TABLE ( ID, IMAGE, DESCRIPTION )
             VALUES ( in_ID, local_IMAGE, in_DESCRIPTION );
         END IF;
         --
         -- Select interMedia object(s) for update.
         --
         SELECT mtbl.IMAGE INTO local_IMAGE
           FROM CW_IMAGES_TABLE mtbl WHERE mtbl.ID = in_ID FOR UPDATE;
         --
         -- Store media data for the column in_IMAGE.
         --
         IF in_IMAGE IS NOT NULL
         THEN
           SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
                  upload_size, upload_mimetype, upload_blob
             FROM CW_IMAGE_UPLOAD_TABLE dtbl WHERE dtbl.name = in_IMAGE;
           IF upload_size &gt; 0
           THEN
             dbms_lob.copy( local_IMAGE.source.localData,
                            upload_blob,
                            upload_size );
             local_IMAGE.setLocal();
             BEGIN
               local_IMAGE.setProperties();
             EXCEPTION
               WHEN OTHERS THEN
                 local_IMAGE.contentLength := upload_size;
                 local_IMAGE.mimeType := upload_mimetype;
             END;
           END IF;
           DELETE FROM CW_IMAGE_UPLOAD_TABLE dtbl WHERE dtbl.name = in_IMAGE;
         END IF;
         --
         -- Update interMedia objects in the table.
         --
         UPDATE CW_IMAGES_TABLE mtbl
           SET mtbl.IMAGE = local_IMAGE
           WHERE mtbl.ID = in_ID;
         --
         -- Display the template completion message.
         --
         htp.print( '&lt;html&gt;' );
         htp.print( '&lt;title&gt;interMedia Code Wizard: Template Upload
       Procedure&lt;/title&gt;' );
         htp.print( '&lt;body&gt;' );
         htp.print( '&lt;h2&gt;&lt;i&gt;inter&lt;/i&gt;Media Code Wizard:
       Template Upload Procedure&lt;/h2&gt;' );
         htp.print( 'Media uploaded successfully.' );
         htp.print( '&lt;/body&gt;' );
         htp.print( '&lt;/html&gt;' );
       END UPLOAD_CW_IMAGES_TABLE_IMAGE;

   This sample image upload procedure declares the following input parameters and variables:

   1. In the declaration section, the procedure declares three input parameters: `in_ID`, `in_IMAGE`, and `in_DESCRIPTION`, then initializes the latter two to `NULL`.

   2. In the subprogram section, the following variables are declared:

      - The variable `local_IMAGE` is assigned the data type ORDSYS.ORDIMAGE and initialized with an empty BLOB using the ORDIMAGE.init( ) method.

      - The variable `local_ID` takes the same data type as the `ID` column in the table `CW_IMAGES_TABLE` and is initialized to `NULL`.

      - Three additional variables are declared `upload_size`, `upload_mimetype`, and `upload_blob`, which are later given values from comparable column names `doc_size`, `mime_type`, and `blob_content` from the document table `CW_IMAGE_UPLOAD_TABLE`, using a SELECT statement in preparation for copying the content of the image BLOB data to the ORDSYS.ORDIMAGE.source.localData attribute.

   Within the outer BEGIN...END executable statement section, the following operations are executed:

   1. Update the existing row in the table `CW_IMAGES_TABLE` for the `IMAGE` and `DESCRIPTION` columns and return the value of `local_ID` where the value of the `ID` column is the value of the `in_ID` input parameter.

   2. If the value returned of `local_ID` is `NULL`, conditionally insert a new row into the table `CW_IMAGES_TABLE` and initialize the instance of the ORDImage object type in the `image` column with an empty BLOB.

   3. Select the ORDImage object column `IMAGE` in the table `CW_IMAGES_TABLE` for update where the value of the `ID` column is the value of the `in_ID` input parameter.

   4. Select a row for the `doc_size`, `mime_type`, and `blob_content` columns from the document table and pass the values to the `upload_size`, `upload_mimetype`, and `upload_blob` variables where the value of the document table `Name` column is the value of the `in_IMAGE` input parameter.

   5. Perform a DBMS_LOB copy of the BLOB data from the table `CW_IMAGE_UPLOAD_TABLE` into the ORDSYS.ORDIMAGE.source.localData attribute, then call the setLocal( ) method to indicate that the image data is stored locally in the BLOB, and ORDImage methods should look for corresponding data in the source.localData attribute.

   6. In the inner executable block, call the ORDImage setProperties( ) method to read the image data to get the values of the object attributes and store them in the image object attributes for the ORDImage object.

   7. If the setProperties( ) call fails, catch the exception and call the contentLength( ) method to get the size of the image and call the mimeType( ) method to get the MIME type of the image.

   8. Delete the row of data from the document table `CW_IMAGE_UPLOAD_TABLE` that was copied to the row in the table `CW_IMAGES_TABLE` where the value of the `Name` column is the value of the `in_IMAGE` input parameter.

   9. Update the ORDImage object `IMAGE` column in the table `CW_IMAGES_TABLE` with the content of the variable `local_IMAGE` where the value of the `ID` column is the value of the `in_ID` input parameter.

   10. Display a completion message on the HTML page to indicate that the media uploaded successfully using the `htp.print` function from the PL/SQL Web Toolkit.

Step 6 Create and test a media retrieval.

Select Create media retrieval procedure from the Main menu, then click Next.

1. Select the database table and procedure type.

   1. Click CW_IMAGES_TABLE.

   2. Click Standalone procedure.

   3. Click Next.

2. Select the media column and key column.

   1. Click IMAGE (ORDIMAGE).

   2. Click ID (Primary key).

   3. Click Next.

3. Select the procedure name and parameter name.

   1. Accept the procedure name provided, `GET_CW_IMAGES_TABLE_IMAGE`.

   2. Accept the parameter name provided, `MEDIA_ID`.

   3. Click Create procedure in the database.

   4. Click Next.

4. Review the following selected procedure creation options:

5.     Procedure type:        Standalone
       Table name:            CW_IMAGES_TABLE
       Media column(s):       IMAGE (ORDIMAGE)
       Key column:            ID
       Procedure name:        GET_CW_IMAGES_TABLE_IMAGE
       Parameter Name:        MEDIA_ID
       Function:              Create procedure in the database

   Click Next.

6. Compile the procedure and review the generated source.

   The Code Wizard displays the following message: `Procedure created successfully: GET_CW_IMAGES_TABLE_IMAGE`

   1. Click View to view the generated source in another window. Close the window after looking at the generated source. A copy of the generated source is shown at the end of Step 6, substep 5e.

   2. Review the URL format used to retrieve images using the `GET_CW_IMAGES_TABLE_IMAGE` procedure.

   3. Enter the number `1` as the Key parameter, then click Test to test the procedure by retrieving the image uploaded previously.

      The retrieved image is displayed in another window.

   4. Close the window.

   5. Click Done to return to the Main menu.

   A copy of the generated image retrieval procedure is as follows:

       CREATE OR REPLACE PROCEDURE GET_CW_IMAGES_TABLE_IMAGE (
        MEDIA_ID IN VARCHAR2 )
       AS
         localObject ORDSYS.ORDIMAGE;
         localBlob  BLOB;
         localBfile BFILE;
         httpStatus NUMBER;
         lastModDate VARCHAR2(256);
       BEGIN
         --
         -- Retrieve the object from the database into a local object.
         --
         BEGIN
           SELECT mtbl.IMAGE INTO localObject FROM CW_IMAGES_TABLE mtbl       WHERE mtbl.ID = MEDIA_ID;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             ordplsgwyutil.resource_not_found( 'MEDIA_ID', MEDIA_ID );
             RETURN;
         END;

         --
         -- Check the update time if the browser sent an If-Modified-Since header.
         --
         IF ordplsgwyutil.cache_is_valid( localObject.getUpdateTime() )
         THEN
           owa_util.status_line( ordplsgwyutil.http_status_not_modified );
           RETURN;
         END IF;

         --
         -- Figure out where the image is.
         --
         IF localObject.isLocal() THEN
           --
           -- Data is stored locally in the localData BLOB attribute.
           --
           localBlob := localObject.getContent();
           owa_util.mime_header( localObject.getMimeType(), FALSE );
           ordplsgwyutil.set_last_modified( localObject.getUpdateTime() );
           owa_util.http_header_close();
           IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) &lt;&gt; 'HEAD' THEN
             wpg_docload.download_file( localBlob );
           END IF;
         ELSIF UPPER( localObject.getSourceType() ) = 'FILE' THEN

           --
           -- Data is stored as a file from which ORDSource creates
           -- a BFILE.
           --
           localBfile  := localObject.getBFILE();
           owa_util.mime_header( localObject.getMimeType(), FALSE );
           ordplsgwyutil.set_last_modified( localObject.getUpdateTime() );
           owa_util.http_header_close();
           IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) &lt;&gt; 'HEAD' THEN
             wpg_docload.download_file( localBfile );
           END IF;

         ELSIF UPPER( localObject.getSourceType() ) = 'HTTP' THEN    --
           -- The image is referenced as an HTTP entity, so we have to
           -- redirect the client to the URL that ORDSource provides.
           --
           owa_util.redirect_url( localObject.getSource() );
         ELSE
           --
           -- The image is stored in an application-specific data
           -- source type for which no default action is available.
           --
           NULL;
         END IF;
       END GET_CW_IMAGES_TABLE_IMAGE;

   This sample image retrieval procedure declares the following input parameters and variables:

   1. In the declaration section, the procedure declares one input parameter: `MEDIA_ID`.

   2. In the subprogram section, the following variables are declared:

      - The variable `localObject` is assigned the data type ORDSYS.ORDIMAGE.

      - The variable `localBlob` is a BLOB data type, the variable `localBfile` is a BFILE data type, `httpStatus` is a NUMBER, and `lastModDate` is a VARCHAR2 with a maximum size of 256 characters.

   Within the outer BEGIN...END executable statement section, the following operations are executed:

   1. Select the ORDImage object column `IMAGE` in the table `CW_IMAGES_TABLE` where the value of the `ID` column is the value of the `MEDIA_ID` input parameter.

   2. In the inner executable block, when no data is found, raise an exception and call the `resource_not_found` function of the PL/SQL Gateway and get the value of the `MEDIA_ID` input parameter.

   3. Check the update time if the browser sent an If-Modified-Since header by calling the getUpdateTime( ) method passed into the `cache_is_valid` function of the PL/SQL Gateway.

   4. If the cache is valid, send an HTTP status code to the client using the PL/SQL Web Toolkit `owa_util` package `status_line` procedure passing in the call to the `http_status_not_modified` function.

   5. Determine where the image data is stored; call the ORDImage isLocal( ) method, which returns a Boolean expression of true if the image data is stored locally in the BLOB, then get the handle to the local BLOB.

      - If the value is true, assign the variable `localBlob` the ORDImage getContent( ) method to get the handle of the local BLOB containing the image data.

      - Call the ORDImage getMimeType( ) method to determine the image's MIME type and pass this to the `owa_util.mime_header` procedure and keep the HTTP header open.

      - Call the ORDImage getUpdateTime( ) method to get the time the image was last modified and pass this to the `ordplsgwyutil.set_last_modified` procedure.

      - Close the HTTP header by calling the `owa_util.http_header_close( )` procedure.

      - Call the `owa_util.get_cgi_env` procedure and if the value of the request method is not `HEAD`, then use the `wpg_docload.download_file` procedure to pass in the value of `localBlob` that contains the LOB locator of the BLOB containing the image data to download the image from the database.

   6. If the ORDImage isLocal( ) method returns false, call the ORDImage getSourceType( ) method to determine if the value is `FILE`; if so, then the image data is stored as an external file on the local file system. Then, get the LOB locator of the BFILE containing the image data.

      - Assign the variable `localBfile` the ORDImage getBfile( ) method to get the LOB locator of the BFILE containing the image data.

      - Call the ORDImage getMimeType( ) method to determine the image's MIME type and pass this to the `owa_util.mime_header` procedure and keep the HTTP header open.

      - Call the ORDImage getUpdateTime( ) method to get the time the image was last modified and pass this to the `ordplsgwyutil.set_last_modified` procedure.

      - Close the HTTP header by calling the `owa_util.http_header_close()` procedure.

      - Call the `owa_util.get_cgi_env` procedure and if the value of the request method is not `HEAD`, then use the `wpg_docload.download_file` procedure to pass in the value of `localBfile` that contains the LOB locator of the BFILE containing the image data to download the image from the file.

   7. If the ORDImage isLocal( ) method returns false, call the ORDImage getSourceType( ) method to determine if the value is `HTTP`; if so, then the image data is stored at an HTTP URL location, which then redirects the client to the URL that ORDSource provides using the `owa_util.redirect_url` procedure.

   8. If the ORDImage isLocal( ) method returns false, call the ORDImage getSourceType( ) method to determine if the value is `FILE` or `HTTP`; if it is neither, then the image is stored in an application-specific data source type that is not recognized or supported by interMedia.

### 3.3.3 Sample Session Using Multiple Object Columns

The following sample session uses the `SCOTT` schema to illustrate the creation of a multimedia upload (multiple interMedia object columns) and single media retrieval procedures. Substitute a different schema name if you want to use a different schema.This sample session assumes the interMedia Code Wizard has been installed.

Perform the following steps:

Step 1 Create a table to store audio for the application by starting SQL*Plus and connecting to the `SCOTT` schema in the database.

For example:

    sqlplus SCOTT/TIGER[@<connect_identifer>]

    SQL> CREATE TABLE cw_media_table(id NUMBER PRIMARY KEY,
                                     description VARCHAR2(30) NOT NULL,
                                     location VARCHAR2(30),
                                     image ORDSYS.ORDIMAGE,
                                     thumb ORDSYS.ORDIMAGE,
                                     audio ORDSYS.ORDAUDIO,
                                     video ORDSYS.ORDVIDEO,
                                     media ORDSYS.ORDDOC);

Step 2 Use the `SCOTTW` DAD you created in Step 2, and then, authorized the use of it in Step 3, of Section 3.3.2.

If you have not created the `SCOTTW` DAD and authorized the use of this DAD, perform Steps 2 and 3 in Section 3.3.2, then continue to next step that follows in this section, Step 3.

Step 3 Change DADs to the `SCOTTCW` DAD.

1. Enter the Code Wizard's administration URL into your browser's location bar, then enter the `ORDSYS` user name and password when prompted by the browser, for example:

       http://<hostname>:<port-number>/ordcwadmin

2. Click Change DAD from the Code Wizard's Main menu.

3. Click Change to SCOTTCW, if it is not already selected, then click Next.

4. Enter the user name `SCOTT` and password `TIGER` when prompted for user name and password, then press OK.

   The Main menu now displays the current DAD as `SCOTTCW` and the current schema as `SCOTT`.

Step 4 Create and test the media upload procedure.

Click Create media upload procedure from the Main menu, then click Next.

1. Select the database table and procedure Type.

   1. Click CW_MEDIA_TABLE.

   2. Click Standalone procedure.

   3. Click Next.

2. Select the PL/SQL document upload table.

   If there are no document tables in the `SCOTT` schema, the Code Wizard displays a message indicating this situation. In this case, accept the default table name provided, `CW_MEDIA_UPLOAD_TABLE`, then click Next.

   If there are existing document tables, but the table `CW_MEDIA_UPLOAD_TABLE` is not among them, click Create new document table, accept the default table name provided, `CW_MEDIA_UPLOAD_TABLE`, then click Next.

   If the `CW_MEDIA_UPLOAD_TABLE` document table already exists, select Use existing document table and CW_MEDIA_UPLOAD_TABLE, then click Next.

3. Select the data access and media columns.

   1. Ensure that IMAGE (ORDIMAGE), THUMB (ORDIMAGE) , AUDIO (ORDAUDIO), VIDEO (ORDVIDEO), and MEDIA (ORDDOC) are all checkmarked.

   2. Click ID (Primary key).

   3. Click Conditional insert or update.

   4. Click Next.

4. Select additional columns and procedure names.

   1. Ensure that DESCRIPTION is checkmarked because this column has a `NOT NULL` constraint. (The LOCATION column is not checkmarked by default as there are no constraints on this column.)

   2. Accept the procedure name provided, `UPLOAD_CW_MEDIA_TABLE_IMAGE`.

   3. Click Create procedure in the database.

   4. Click Next.

5. Review the following selected procedure creation options that are displayed:

       Procedure type:        Standalone
       Table name:            CW_MEDIA_TABLE
       Media column(s):       IMAGE (ORDIMAGE)
                              THUMB (ORDIMAGE)
                              AUDIO (ORDAUDIO)
                              VIDEO (ORDVIDEO)
                              MEDIA (ORDDOC)
       Key column:            ID
       Additional column(s):  DESCRIPTION
       Table access mode:     Conditional update or insert
       Procedure name:        UPLOAD_CW_MEDIA_TABLE_IMAGE
       Function:              Create procedure in the database

   Click Finish.

6. Compile the procedure and review the generated source information.

   The Code Wizard displays the following message: "`Procedure created successfully: UPLOAD_CW_MEDIA_TABLE_IMAGE`".

   1. At the option Click to display generated source:, click View to view the generated source in another window. A copy of the generated source is shown at the end of Step 4, substep 6g.

   2. Close the window after looking at the generated source.

   3. Accept the DAD: name provided, `SCOTTCW`, then click Test to display in another window a template file upload form that you can use to test the generated procedure.

   4. To customize the template file upload form, select Save As... from your browser's File pull-down menu to save the HTML source for editing.

   5. To test the template upload form, enter the following information:

      - For the ID: column, enter the number `1` as the row's primary key.

      - For each interMedia object column, click Browse... and choose the appropriate media to upload to each column of the table. You can choose one or more or all columns to test.

      - For the DESCRIPTION column, enter a brief description of the media.

      - Click Upload media.

      The Code Wizard displays a template completion window with the heading inter Media Code Wizard: Template Upload Procedure, and, if the procedure is successful, the message: `Media uploaded successfully`.

   6. Close the window.

   7. Click Done on the Compile procedure and review generated source window to return to the Main menu of the Code Wizard.

   A copy of the generated multimedia upload procedure is as follows:

       CREATE OR REPLACE PROCEDURE UPLOAD_CW_MEDIA_TABLE_IMAGE
         ( in_ID IN VARCHAR2,
           in_IMAGE IN VARCHAR2 DEFAULT NULL,
           in_THUMB IN VARCHAR2 DEFAULT NULL,
           in_AUDIO IN VARCHAR2 DEFAULT NULL,
           in_VIDEO IN VARCHAR2 DEFAULT NULL,
           in_MEDIA IN VARCHAR2 DEFAULT NULL,
           in_DESCRIPTION IN VARCHAR2 DEFAULT NULL )
       AS
         local_IMAGE ORDSYS.ORDIMAGE := ORDSYS.ORDIMAGE.init();
         local_THUMB ORDSYS.ORDIMAGE := ORDSYS.ORDIMAGE.init();
         local_AUDIO ORDSYS.ORDAUDIO := ORDSYS.ORDAUDIO.init();
         local_AUDIO_ctx RAW( 64 );
         local_VIDEO ORDSYS.ORDVIDEO := ORDSYS.ORDVIDEO.init();
         local_VIDEO_ctx RAW( 64 );
         local_MEDIA ORDSYS.ORDDOC := ORDSYS.ORDDOC.init();
         local_MEDIA_ctx RAW( 64 );
         local_ID CW_MEDIA_TABLE.ID%TYPE := NULL;
         upload_size     INTEGER;
         upload_mimetype VARCHAR2( 128 );
         upload_blob     BLOB;
       BEGIN
         --
         -- Update the existing row.
         --
         UPDATE CW_MEDIA_TABLE mtbl
           SET mtbl.IMAGE = local_IMAGE,
               mtbl.THUMB = local_THUMB,
               mtbl.AUDIO = local_AUDIO,
               mtbl.VIDEO = local_VIDEO,
               mtbl.MEDIA = local_MEDIA,
               mtbl.DESCRIPTION = in_DESCRIPTION
           WHERE mtbl.ID = in_ID
           RETURN mtbl.ID INTO local_ID;
         --
         -- Conditionally insert a new row if no existing row is updated.
         --
         IF local_ID IS NULL
         THEN
           --
           -- Insert a new row into the table.
           --
           INSERT INTO CW_MEDIA_TABLE ( ID, IMAGE, THUMB, AUDIO, VIDEO, MEDIA,
        DESCRIPTION )
             VALUES ( in_ID, local_IMAGE, local_THUMB, local_AUDIO,
                      local_VIDEO, local_MEDIA, in_DESCRIPTION );
         END IF;
         --
         -- Select interMedia object(s) for update.
         --
         SELECT mtbl.IMAGE, mtbl.THUMB, mtbl.AUDIO, mtbl.VIDEO, mtbl.MEDIA INTO
        local_IMAGE, local_THUMB, local_AUDIO, local_VIDEO, local_MEDIA
           FROM CW_MEDIA_TABLE mtbl WHERE mtbl.ID = in_ID FOR UPDATE;
         --
         -- Store media data for the column in_IMAGE.
         --
         IF in_IMAGE IS NOT NULL
         THEN
           SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
                  upload_size, upload_mimetype, upload_blob
             FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_IMAGE;
           IF upload_size &gt; 0
           THEN
             dbms_lob.copy( local_IMAGE.source.localData,
                            upload_blob,
                            upload_size );
             local_IMAGE.setLocal();
             BEGIN
               local_IMAGE.setProperties();
             EXCEPTION
               WHEN OTHERS THEN
                 local_IMAGE.contentLength := upload_size;
                 local_IMAGE.mimeType := upload_mimetype;
             END;
           END IF;
           DELETE FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_IMAGE;
         END IF;
         --
         -- Store media data for the column in_THUMB.
         --
         IF in_THUMB IS NOT NULL
         THEN
           SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
                  upload_size, upload_mimetype, upload_blob
             FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_THUMB;
           IF upload_size &gt; 0
           THEN
             dbms_lob.copy( local_THUMB.source.localData,
                            upload_blob,
                            upload_size );
             local_THUMB.setLocal();
             BEGIN
               local_THUMB.setProperties();
             EXCEPTION
               WHEN OTHERS THEN
                 local_THUMB.contentLength := upload_size;
                 local_THUMB.mimeType := upload_mimetype;
             END;
           END IF;
           DELETE FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_THUMB;
         END IF;
         --
         -- Store media data for the column in_AUDIO.
         --
         IF in_AUDIO IS NOT NULL
         THEN
           SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
                  upload_size, upload_mimetype, upload_blob
             FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_AUDIO;
           IF upload_size &gt; 0
           THEN
             dbms_lob.copy( local_AUDIO.source.localData,
                            upload_blob,
                            upload_size );
             local_AUDIO.setLocal();
             BEGIN
               local_AUDIO.setProperties(local_AUDIO_ctx);
             EXCEPTION
               WHEN OTHERS THEN
                 local_AUDIO.mimeType := upload_mimetype;
             END;
           END IF;
           DELETE FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_AUDIO;
         END IF;
         --
         -- Store media data for the column in_VIDEO.
         --
         IF in_VIDEO IS NOT NULL
         THEN
           SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
                  upload_size, upload_mimetype, upload_blob
             FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_VIDEO;
           IF upload_size &gt; 0
           THEN
             dbms_lob.copy( local_VIDEO.source.localData,
                            upload_blob,
                            upload_size );
             local_VIDEO.setLocal();
             BEGIN
               local_VIDEO.setProperties(local_VIDEO_ctx);
             EXCEPTION
               WHEN OTHERS THEN
                 local_VIDEO.mimeType := upload_mimetype;
             END;
           END IF;
           DELETE FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_VIDEO;
         END IF;
         --
         -- Store media data for the column in_MEDIA.
         --
         IF in_MEDIA IS NOT NULL
         THEN
           SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
                  upload_size, upload_mimetype, upload_blob
             FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_MEDIA;
           IF upload_size &gt; 0
           THEN
             dbms_lob.copy( local_MEDIA.source.localData,
                            upload_blob,
                            upload_size );
             local_MEDIA.setLocal();
             BEGIN
               local_MEDIA.setProperties(local_MEDIA_ctx, FALSE);
             EXCEPTION
               WHEN OTHERS THEN
                 local_MEDIA.contentLength := upload_size;
                 local_MEDIA.mimeType := upload_mimetype;
             END;
           END IF;
           DELETE FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_MEDIA;
         END IF;
         --
         -- Update interMedia objects in the table.
         --
         UPDATE CW_MEDIA_TABLE mtbl
           SET mtbl.IMAGE = local_IMAGE,
               mtbl.THUMB = local_THUMB,
               mtbl.AUDIO = local_AUDIO,
               mtbl.VIDEO = local_VIDEO,
               mtbl.MEDIA = local_MEDIA
           WHERE mtbl.ID = in_ID;
         --
         -- Display the template completion message.
         --
         htp.print( '&lt;html&gt;' );
         htp.print( '&lt;title&gt;interMedia Code Wizard: Template Upload
        Procedure&lt;/title&gt;' );
         htp.print( '&lt;body&gt;' );
         htp.print( '&lt;h2&gt;&lt;i&gt;inter&lt;/i&gt;Media Code Wizard:
        Template Upload Procedure&lt;/h2&gt;' );
         htp.print( 'Media uploaded successfully.' );
         htp.print( '&lt;/body&gt;' );
         htp.print( '&lt;/html&gt;' );

       END UPLOAD_CW_MEDIA_TABLE_IMAGE;

   This sample multimedia upload procedure declares the following input parameters and variables:

   1. In the declaration section, the procedure declares seven input parameters: `in_ID`, `in_IMAGE`, `in_THUMB`, `in_AUDIO`, `in_VIDEO`, `in_MEDIA`, and `in_DESCRIPTION`, then initializes the last six to `NULL`.

   2. In the subprogram section, the following variables are declared:

      - The variables `local_IMAGE` and `local_THUMB` are assigned the data type ORDSYS.ORDIMAGE and initialized with an empty BLOB using the ORDIMAGE.init( ) method.

      - The variable `local_AUDIO` is assigned the data type ORDSYS.ORDAUDIO and initialized with an empty BLOB using the ORDAUDIO.init( ) method. Also a context variable `local_AUDIO_ctx` is assigned the data type RAW(64).

      - The variable `local_VIDEO` is assigned the data type ORDSYS.ORDVIDEO and initialized with an empty BLOB using the ORDVIDEO.init( ) method. Also, a context variable `local_VIDEO_ctx` is assigned the data type RAW(64).

      - The variable `local_MEDIA` is assigned the data type ORDSYS.ORDDOC and initialized with an empty BLOB using the ORDDOC.init( ) method. Also, a context variable `local_MEDIA_ctx` is assigned the data type RAW(64).

      - The variable `local_ID` takes the same data type as the `ID` column in the table `CW_MEDIA_TABLE` and is initialized to `NULL`.

      - Three additional variables are declared `upload_size`, `upload_mimetype`, and `upload_blob`, which are later given values from comparable column names `doc_size`, `mime_type`, and `blob_content` from the document table `MEDIA_UPLOAD_TABLE` using a SELECT statement. This is all in preparation for copying the content of the image, thumb, audio, video, and media BLOB data to the respective ORDSYS.ORDIMAGE.source.localData, ORDSYS.ORDIMAGE.source.localData, ORDSYS.ORDAUDIO.source.localData, ORDSYS.ORDVIDEO.source.localData, and ORDSYS.ORDDOC.source.localData attributes.

   Within the outer BEGIN...END executable statement section, the following operations are executed:

   1. Update the existing row in the table `CW_MEDIA_TABLE` for the `IMAGE` , `THUMB`, `AUDIO`, `VIDEO`, `MEDIA`, and `DESCRIPTION` columns and return the value of `local_ID` where the value of the `ID` column is the value of the `in_ID` input parameter.

   2. If the value returned of `local_ID` is `NULL`, conditionally insert a new row into the table `CW_MEDIA_TABLE` and initialize the instance of the ORDImage object type in the `IMAGE` column with an empty BLOB, the instance of the ORDImage object type in the `THUMB` column with an empty BLOB, the instance of the ORDAudio object type in the `AUDIO` column with an empty BLOB, the instance of the ORDVideo object type in the `VIDEO` column with an empty BLOB, and the instance of the ORDDoc object type in the `MEDIA` column with an empty BLOB.

   3. Select the ORDImage object column `IMAGE`, ORDImage object column `THUMB`, ORDAudio object column `AUDIO`, ORDVideo object column `VIDEO`, and ORDDoc object column `MEDIA` in the table `CW_MEDIA_TABLE` for update where the value of the `ID` column is the value of the `in_ID` input parameter.

   4. Select a row for the `doc_size`, `mime_type`, and `blob_content` columns from the document table and pass the values to the `upload_size`, `upload_mimetype`, and `upload_blob` variables where the value of the `Name` column is the value of one of the following input parameters `in_IMAGE`; `in_THUMB`; `in_AUDIO`; `in_VIDEO`; or `in_MEDIA`.

   5. Perform a DBMS LOB copy of the BLOB data from the table `MEDIA_UPLOAD_TABLE` into the ORDSYS.ORDIMAGE.source.localData, ORDSYS.ORDIMAGE.source.localData, ORDSYS.ORDAUDIO.source.localData, ORDSYS.ORDVIDEO.source.localData, and ORDSYS.ORDDoc.source.localData attribute, then call the setLocal( ) method to indicate that the image, audio, and video data are stored locally in the BLOB, and ORDImage, ORDAudio, ORDVideo, and ORDDoc methods should look for corresponding data in the source.localData attribute.

   6. In the inner executable block, call the respective ORDImage, ORDAudio, ORDVideo, and ORDDoc setProperties( ) method to read the image, audio, and video data to get the values of the object attributes and store them in the image, audio, video, and media object attributes for the ORDImage, ORDAudio, ORDVideo, and ORDDoc objects.

   7. If the setProperties( ) call fails, catch the exception and call the contentLength( ) method to get the size of the media data and call the mimeType( ) method to get the MIME type of the media data.

   8. Delete the row of data from the document table `MEDIA_UPLOAD_TABLE` hat was copied to the row in the table `CW_MEDIA_TABLE` where the value of the `Name` column is the value of the respective `in_IMAGE`, `in_THUMB`, `in_AUDIO`, `in_VIDEO`, and `in_MEDIA` input parameter.

   9. Update the ORDImage object `IMAGE` column, the ORDImage object `THUMB` column, the ORDAudio object `AUDIO` column, the ORDVideo object `VIDEO` column, and the ORDDoc object `MEDIA` column in the table `CW_MEDIA_TABLE` with the content of the variables `local_IMAGE`, `local_THUMB`, `local_AUDIO`, `local_VIDEO`, and `local_MEDIA` respectively, where the value of the `ID` column is the value of the `in_ID` input parameter.

   10. Display a completion message on the HTML page to indicate that the media uploaded successfully using the `htp.print` function from the PL/SQL Web Toolkit.

Step 5 Create and test a media retrieval.

Select Create media retrieval procedure from the Main menu, then click Next.

1. Select the database table and procedure type.

   1. Click CW_MEDIA_TABLE.

   2. Click Standalone procedure.

   3. Click Next.

2. Select the media column and key column.

   1. Ensure that one the following object columns is checkmarked. For example, if you loaded media data into the media column in Step 4, substep 6e, then select the MEDIA (ORDDOC) column.

   2. Click ID (Primary key).

   3. Click Next.

3. Select the procedure name and parameter name.

   1. Accept the procedure name provided, `GET_CW_MEDIA_TABLE_IMAGE`.

   2. Accept the parameter name provided, `MEDIA_ID`.

   3. Click Create procedure in the database.

   4. Click Next.

4. Review the following selected procedure creation options:

5.     Procedure type:        Standalone
       Table name:            CW_MEDIA_TABLE
       Key column:            ID
       Media column:          IMAGE (ORDDOC)
       Procedure name:        GET_CW_MEDIA_TABLE_IMAGE
       Parameter name:        MEDIA_ID
       Function:              Create procedure in the database

   Click Finish.

6. Compile the procedure and review the generated source.

   The Code Wizard displays the following message: `Procedure created successfully: GET_CW_MEDIA_TABLE_IMAGE`.

   1. Click View to view the generated source in another window. Close the window after looking at the generated source. A copy of the generated source is shown at the end of this step.

   2. Review the URL format used to retrieve images using the `GET_CW_MEDIA_TABLE_IMAGE` procedure.

   3. Enter the number `1` as the Key parameter, then click Test to test the procedure by retrieving the image uploaded previously.

   4. The retrieved image is displayed in another window.

   5. Close the window.

   6. Click Done to return to the Main menu.

   Note: A generated media retrieval script, unlike the multiple media upload script shown at the end of Step 4, handles only the type of media data designed for that interMedia object type. To retrieve media data stored in other interMedia object types, generate a retrieval script for each desired media data type and add it to your PL/SQL package.

   A copy of the generated media retrieval procedure is as follows:

       CREATE OR REPLACE PROCEDURE GET_CW_MEDIA_TABLE_MEDIA ( MEDIA_ID
        IN VARCHAR2 )
       AS
         localObject ORDSYS.ORDDOC;
         localBlob  BLOB;
         localBfile BFILE;
         httpStatus NUMBER;
         lastModDate VARCHAR2(256);

       BEGIN
         --
         -- Retrieve the object from the database into a local object.
         --
         BEGIN
           SELECT mtbl.MEDIA INTO localObject FROM CW_MEDIA_TABLE mtbl
             WHERE mtbl.ID = MEDIA_ID;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             ordplsgwyutil.resource_not_found( 'MEDIA_ID', MEDIA_ID );
             RETURN;
         END;
         --  -- Check the update time if the browser sent an If-Modified-Since header.
         --
         IF ordplsgwyutil.cache_is_valid( localObject.getUpdateTime() )
         THEN
           owa_util.status_line( ordplsgwyutil.http_status_not_modified );
           RETURN;
         END IF;
         --
         -- Figure out where the image is.
         --
         IF localObject.isLocal() THEN
           --
           -- Data is stored locally in the localData BLOB attribute.
           --
           localBlob := localObject.getContent();
           owa_util.mime_header( localObject.getMimeType(), FALSE );
           ordplsgwyutil.set_last_modified( localObject.getUpdateTime() );
           owa_util.http_header_close();
           IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) &lt;&gt; 'HEAD' THEN
             wpg_docload.download_file( localBlob );
           END IF;

         ELSIF UPPER( localObject.getSourceType() ) = 'FILE' THEN
           --
           -- Data is stored as a file from which ORDSource creates
           -- a BFILE.
           --
           localBfile  := localObject.getBFILE();
           owa_util.mime_header( localObject.getMimeType(), FALSE );
           ordplsgwyutil.set_last_modified( localObject.getUpdateTime() );
           owa_util.http_header_close();
           IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) &lt;&gt; 'HEAD' THEN
             wpg_docload.download_file( localBfile );
           END IF;

         ELSIF UPPER( localObject.getSourceType() ) = 'HTTP' THEN
           --
           -- The image is referenced as an HTTP entity, so we have to
           -- redirect the client to the URL that ORDSource provides.
           --
           owa_util.redirect_url( localObject.getSource() );
         ELSE
           --
           -- The image is stored in an application-specific data
           -- source type for which no default action is available.
           --
           NULL;
         END IF;
       END GET_CW_MEDIA_TABLE_MEDIA;

   See the description at the end of the generated image retrieval procedure in Section 3.3.2, Step 6 "Create and test a media retrieval", after substep 5e. The only difference between these two retrieval procedures is the type of object being retrieved, an ORDImage object type versus an ORDDoc object type.

### 3.3.4 Known Restrictions of the Oracle interMedia Code Wizard

The following restrictions are known for the interMedia Code Wizard:

- Tables with composite primary keys are not supported.

  To use a table with a composite primary key, create an upload or download procedure, then edit the generated source to support all the primary key columns. For example, for a media retrieval procedure, this might involve adding an additional parameter, then specifying that parameter in the `where` clause of the SELECT statement.

- User object types containing embedded interMedia object types are not recognized by the interMedia Code Wizard.